Get all values from pivot table based on another column in same pivot table

I was wondering how to get all the values from a pivot table based on another column from the same table. For example, let's consider we have two tables i.e

Product
User
product_user (Pivot table)

The pivot table column are: product_user 1. user_id 2. product_id 3. type (value can be 1 or 2)

Say I want to retrieve all the rows from the table where user_id is 1 and type is also 1

Example data:

user_id product_id type 1 1 1 1 3 1 1 76 1 2 2 2 1 21 1 1 33 2 1 23 2 1 25 1

Expected result is all the list of products based on user_id as 1 and type 1

MichalOravec

5 years ago

Best Answer

Level 75

It should be like this

$userId = 1;

$type = 1;

$products = Product::whereHas('users', function ($query) use ($userId, $type) {
    $query->where('id', $userId)->where('product_user.type', $type);
})->get();

1 like

sumitp302

5 years ago

Level 1

Thank you so much! This helped a lot. Although this answer looks perfect, but I also found an alternate way:

$userId = 1;
$type = 1;

$user = User::where('id', $userId)->firstOrFail();
$products = $user->products() ->where('type', $type)->get();

Considering that products() many-to-many relation is set Inside User Model:

public function products() {
            return $this->belongsToMany(Product::class)
                ->withPivot(['type'])
                ->withTimestamps();
        }

Please or to participate in this conversation.