Counting matching foreign keys in pivot table

Hi, First of all you don't need to add that counting field in the pivot table, because otherwise you don't need a pivot table, that would become a 1:n relation, or a 1:1 if there's one unique display per user. So if you use your pivot table correctly, you should be able to do such thing :

<?php 

$specificDisplayArray = array();

foreach($customer->displays as $display) {
    if($display->id == specific_display_id)
        array_push($specificDisplayArray, $display);
}

count($specificDisplayArray);

There's probably a cleaner way to do it though, but that's just to give you an idea of how you can get it done. You also need to setup your pivot table correctly (see the docs).

Hi transpalette and thank you very much for your reply! You have given me a couple of ideas but I don't believe this is a solution to my problem, it's my fault for not explaining properly. I realise there should not be an activation column in the pivot table and wish to remove it. It is a many-to-many as any of the displays can be activated by any of the users. I'm also pretty certain my pivot table is set up correctly as is functioning as it should, although I will show some code . So this is my display model.

class Display extends Model
{
    public function customers(){
        return $this->belongsToMany(Customer::class)->withTimestamps()->withPivot('activations');
    }
}

This is my Customer model

class Customer extends Model
{
    protected $fillable = [
        'name', 'email',
    ];

    public function displays(){
        return $this->belongsToMany(Display::class)->withTimestamps()->withPivot('activations');
    }
}

Here is my Display controller

class DisplayController extends Controller
{
    public function __construct(){
        $this->middleware('admin2');
    }

    public function index(){
        //finding all displays from database
        $displays = Display::all();
        //sending the results to the view
        return view('displays.displays', compact('displays'));
    }

    public function show(Display $display){
        //eager loading the customers linked to the display
        $display->load('customers');
        //sending the single display and it's linked customers to the view
        return view('displays.show',compact('display'));
    }
}

And this is the show view

@extends('layouts.app')
@section('content')
    <h1>{{$display->location}}</h1>
    <ul>
        @foreach($display->customers as $customer)
            <a href ="../customers/{{$customer->id}}"> <li><p>Customer: {{$customer->name}} Activations:{{$customer->pivot->activations}} </p></li></a>
        @endforeach
    </ul>
@endsection

As you can see the Activation count is currently getting the data from the activation's column in the pivot table which I know is incorrect. I wish to remove the activation column altogether and simply count the amount of times that the matching foreign keys appear in the pivot table. So if a customer with id 2 activates a display with id 6 twice, then I can call a function that can count that.

Okay ... So what about something like this ?

$customer->nbActivations($display);

And in your customer model you would have that function :

public function nbActivations(Display $display) {
    $count = 0;

    foreach($display->customers as $customer) {
        if($customer->id == $this->id)
            $count++;
    }

    return $count;
}

Wouldn't this be enough ? Or maybe I'm not visualizing your pivot table correctly ...

Thank you transpalette that worked like a charm :D . I have also had to change my view in order to stop repeated outputs in the list, I achieved this by using an array to put the id's in as it looped (using it as a blacklist). Although it works I'm wondering if this is the best way of doing it, my code is here.

@extends('layouts.app')
@section('content')
    <h1>{{$display->location}}</h1>
    <ul>
        {{--array used to store $customer->is's that have already been looped over--}}
        <?php $used = array();
        foreach($display->customers as $customer){
            //enter if statement only if the id is not already in the array
            if (!in_array($customer->id,$used)){
                ?><a href="../customers/{{$customer->id}}"><li><p>Customer:{{$customer->name}} Activations:{{$customer->nbActivations($display)}}</p></li></a><?php
                //add the $customer->id to the array
                $used[] = $customer->id;
            }
        }?>
    </ul>
@endsection