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?

Solution 1

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

Solution 2

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/

Solution 3

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();

Comments

[vFragosop]

Indeed you are right, sir. I've never used it on where clauses and according to MySQL docs, you can only use column aliases on groupBy, orderBy and having. I've fixed the answer.

[Arda]

Great, this works just as I've expected. Thanks! (p.s: I was not the one who downvoted, but I upvoted)

[Arda]

@vFragshop Also thanks for the detailed clarification, I'm currently saving both solutions on my side just in case.

[Arda]

Sorry, I had to un-select your answer as best answer, because Laravel 4.1 brings new native methods exactly for my question's purpose.