How to filter a pivot table using Eloquent?
I try to setup all relationships in both directions as this allows for use of dynamic properties, eg $user->works()
.
class Collection extends Eloquent { public function contents() { return $this->belongsToMany('Content', 'collection_content', 'collection_id', 'content_id')->withPivot('collection_id', 'group_id', 'field_identifier'); }}class Content extends Eloquent { public function collections() { return $this->belongsToMany('Collection', 'collection_content', 'collection_id', 'content_id')->withPivot('collection_id', 'group_id', 'field_identifier'); }}class CollectionContent extends Eloquent { public function content() { return $this->belongsTo('Content'); } public function collection() { return $this->belongsTo('Collection'); }}
Then query:
$works = User::find(1)->works()->where('active', 1)->get();
Eloquent's documentation is awful when it comes to the use of pivot tables. This is a great tutorial: http://www.developed.be/2013/08/30/laravel-4-pivot-table-example-attach-and-detach/
Whenever you call withPivot('foo')
, Laravel you do:
SELECT ... `table`.`foo` AS `pivot_foo` FROM `table` ...
Fixed Answer:
MySQL in particular allows the usage of column aliases on HAVING
, GROUP BY
and ORDER BY
clauses, but not on WHERE
clauses.
Both HAVING
and WHERE
are used for filtering queries, but they behave slightly different: HAVING
is applied after GROUP BY
and WHERE
is before.
As a general SQL rule, you shouldn't use column aliases (pivot_foo
in that case) for grouping, filtering or anything like that, since it may not work with other SQL databases.
Although not recommended, it's possible to use:
return User::find(1)->works()->having('pivot_active','=','1')->get();