Bervetuna's avatar

wherePivot

There is a many to many relation between my Gebruiker model en my Onkost model.

The pivot table (gebruiker_onkost) has an extra field 'isGefactureerd' (boolean).

The relationships are defined in both the Gebruiker model and the Onkost model.

//Gebruiker model
public function onkosten()
    {
        return $this->belongsToMany(Onkost::class)->withPivot('isGefactureerd');
    }

//Onkost model
public function gebruikers()
    {
        return $this->belongsToMany(Gebruiker::class)->withPivot('isGefactureerd');
    }

In a query I would like to get all the gebruikers whereHas onkosts where isGefactureerd is false.

I thought withPivot would do the trick. My (simplified query) is:

$gebruikers = Gebruiker::whereHas('onkosten', function ($query) use ($thisMonth, $thisYear) {
            $query->whereMonth('datum', '=', $thisMonth)
            ->whereYear('datum', '=', $thisYear)
            ->where('goedgekeurd','=',2)
            ->wherePivot('isGefactureerd',  false, 'gebruiker_onkost');
            
            ;
      })->with(['onkosten' => function ($query) use ($thisMonth, $thisYear) {
            $query->whereMonth('datum', '=', $thisMonth)
            ->whereYear('datum', '=', $thisYear)
            ->where('goedgekeurd','=',2)
            ->wherePivot('isGefactureerd',  false, 'gebruiker_onkost');
            
            ;
        }])->get()

This results in an error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot' in 'where clause'

if I replace the wherePivot with a simple where, the code does work.

I would like to understand why.

Tanks!

0 likes
2 replies
LaryAI's avatar
Level 58

The issue is that the wherePivot method is looking for a column named pivot in the pivot table, but the actual name of the pivot table is gebruiker_onkost. To fix this, you need to pass the name of the pivot table as the third argument to the wherePivot method. Here's the corrected query:

$gebruikers = Gebruiker::whereHas('onkosten', function ($query) use ($thisMonth, $thisYear) {
    $query->whereMonth('datum', '=', $thisMonth)
        ->whereYear('datum', '=', $thisYear)
        ->where('goedgekeurd', '=', 2)
        ->wherePivot('isGefactureerd', false, 'gebruiker_onkost');
}) ->with(['onkosten' => function ($query) use ($thisMonth, $thisYear) {
    $query->whereMonth('datum', '=', $thisMonth)
        ->whereYear('datum', '=', $thisYear)
        ->where('goedgekeurd', '=', 2)
        ->wherePivot('isGefactureerd', false, 'gebruiker_onkost');
}])->get();
Bervetuna's avatar

@LaryAI

That gives the same error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot' in 'where clause'

Please or to participate in this conversation.