Laravel withPivot in subquery doesn't work in when clause-CodePudding

When I apply the wherePivot 'directly' it works, but when I apply it in a when clause it doesn't. I get the error: Column not found: 1054 Unknown column 'pivot' in 'where clause'

return Company::where('owner_id', auth()->user()->id)
    ->with(['subscribers' => function ($q) {
            $q->wherePivot('is_customer', 1); // This works
            $q->when($this->type != 'all', function ($q) {
                $q->wherePivot('is_customer', 1); // This does not
            });
            $q->when($this->example != 'all', function ($q) {
                $q->where('example', 1); // This works
            });
        }
    ])
    ->firstOrFail();

CodePudding user response：

Your first issue was using the same variable $q as the inner and outer query:

Company::where(/* ... */)->with(['subscribers' => function ($q) {
  $q->when($this->type != 'all', function ($q) {
    // ...
  });
});

In the 1st instance, $q is a belongsToMany, as public function subscribers() in Company.php is a Many-to-many relationship, likely defined as return $this->belongsToMany(Subscriber::class); (or similar). When you call $q->wherePivot(/* ... */), this executes a query against the Pivot table's data.

In the 2nd instance, $q is a basic Builder instance. When you call $q->wherePivot(/* ... */), this executes a "magic method" query, available on all Eloquent Models. where{Column}(), like whereId(), whereEmail(), wherePivot(), etc., will execute an equivalent query like WHERE id ..., WHERE email ... and finally WHERE pivot .... Since you don't have a column called pivot this fails.

The solution here is to not use the same variable name, and pass it forward when querying:

return Company::where('owner_id', auth()->user()->id)
->with(['subscribers' => function ($query) {
  $query->when($this->type != 'all', function ($subQuery) use ($query) {
    $query->wherePivot('is_customer', 1);
  });
])
->firstOrFail();

Now, you can clearly see which instance wherePivot() is being called on, $query being the belongsToMany() query, and $subQuery being a Builder instance, and isn't directly used.