How to filter a pivot table using Eloquent?

Question

I'm using a pivot table on the project I'm working with to get works of users.

E.g: User::find(1)->works gives me the works of user with ID of 1.

The thing is that I want to filter this results with extra Pivot data.

Something like:

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

In which the active is the column I've set in my user_works pivot table.

This is my related 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 related 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 schema:

<?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 any way to gain the data without making a new model for the pivot table?

Thanks in advance,

Edit: I can filter this way:

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

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

Answer 1

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/

Answer 2

Laravel 4.1 brings native wherePivot and orWherePivot methods, which is directly a solution to my problem.