How to filter a pivot table using Eloquent?

Question

I use a pivot table in a project I'm working on to work with users.

For example: User::find(1)->works gives me the user's work with identifier 1.

The thing is, I want to filter these results with additional Pivot data.

Something like:

 User::find(1)->works->pivot->where('active',1)->get(); 

In which the column that I set in the user_works pivot table is active.

This is my sister part of my User.php model:

 <?php class User extends Cartalyst\Sentry\Users\Eloquent\User { public function works() { return $this->belongsToMany('Work','user_works')->withPivot('active')->withTimestamps(); } } 

This is my sister part of my Work.php model:

 <?php class Work extends Eloquent { public function users() { return $this->belongsToMany('User','user_works')->withPivot('active')->withTimestamps(); } } 

This is my pivot table:

 <?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; class CreateUserWorksTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('user_works', function(Blueprint $table) { $table->increments('id'); $table->integer('user_id')->unsigned()->default(0); $table->integer('work_id')->unsigned()->default(0); $table->enum('active',array('0','1'))->default(1); $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); $table->foreign('work_id')->references('id')->on('works')->onDelete('cascade'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('user_works'); } } 

Is there a way to get data without creating a new model for the pivot table?

Thanks in advance,

Edit: I can filter this path:

 return User::find(1)->works()->where('user_works.active','=','1')->get(); 

I had to type the name of the raw table. But is there a better way to get this without using it?

Answer 1

Laravel 4.1 provides wherePivot and orWherePivot , which is a direct solution to my problem.

Answer 2

Whenever you call withPivot('foo') , Laravel you do:

 SELECT ... `table`.`foo` AS `pivot_foo` FROM `table` ... 

Fixed answer:

MySQL, in particular, allows column aliases to be used in HAVING , GROUP BY and ORDER BY sections, but not in WHERE clauses.

Both HAVING and WHERE parameters are used to filter queries, but they behave a little differently: HAVING is applied after GROUP BY and WHERE earlier.

As a general SQL rule, you should not use column aliases ( pivot_foo in this case) to group, filter, or anything like that, since it may not work with other SQL databases.

Although not recommended, you can use:

 return User::find(1)->works()->having('pivot_active','=','1')->get(); 

Answer 3

I am trying to establish all relationships in both directions, as this allows us to use dynamic properties like $ 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 the request: $ works = User :: find (1) → works () → where ('active', 1) → get ();

Vibrant documentation is terrible when it comes to using pivot tables. This is a great tutorial: http://www.developed.be/2013/08/30/laravel-4-pivot-table-example-attach-and-detach/