I cannot filter base on pivot table in a many to many relationship

In Laravel 7, I have two tables employees and posts tables as below.

   public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->id();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('business_abn')->nullable();
            $table->string('business_details')->nullable();
            $table->string('stripe_customer_id')->nullable();
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('address_id');
            $table->double('balance')->nullable();
            $table->date('balance_updated_at')->nullable();
            $table->boolean('is_balance_auto_renew')->default(false);
            $table->timestamps();
        });
    }

posts table

    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->text('note')->nullable();
            $table->unsignedBigInteger('employee_id');//id of who posted
            $table->unsignedBigInteger('address_id');
            $table->unsignedBigInteger('service_id');
            $table->unsignedBigInteger('number_of_accepted')->default(0);
            $table->timestamps();
        });
    }

pivot table

    public function up()
    {
        Schema::create('employee_post', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('post_id');
            $table->unsignedBigInteger('employee_id');
            $table->string('status')->nullable();
            $table->timestamps();
        });
    }

an this is the relations

employee model

class Employee extends Model
{
    public function posts()
    {
        return $this->belongsToMany(Post::class)->withPivot('status')->withTimestamps();
    }
}

post model

class Post extends Model
{
    public function employees(){
        return $this->belongsToMany(Employee::class)->withPivot('status')->withTimestamps();
    }
}

Now in my controller I want to fetch a post with all the employees who accepted the post, as below.

    public function show($id)
    {
         $post = Post::whereId($id)
         ->with(['employees' => function($q){
             $q->where('pivot.status','Accepted');
         }])->with('address', 'service', 'options','images')
         ->first();

         return response()->json($post);
    }

But I get the following error

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot.status' in 'where clause' ...

in the above query other column working, if I trye this $q->where('user_id',3) it is working fine.

LaryAI

3 years ago

Level 58

The error message suggests that the "pivot.status" column is not found in the database. This is because the pivot table "employee_post" does not have a "status" column. Instead, the "status" column is defined in the relationship definition as a pivot attribute.

To filter based on the pivot attribute, you need to use the "wherePivot" method instead of "where". Here's the updated code for the controller method:

public function show($id)
{
    $post = Post::whereId($id)
        ->with(['employees' => function($q){
            $q->wherePivot('status', 'Accepted');
        }])
        ->with('address', 'service', 'options', 'images')
        ->first();

    return response()->json($post);
}

Note that we're using "wherePivot" instead of "where" to filter based on the pivot attribute "status". This should solve the error and return the desired result.

Please or to participate in this conversation.