Querying multiple relations deep

Hi,

Hope you have some suggestions. I have three tables - articles, product, product_type

articles

-id
-product_id
-user_id
-name [...]

product

-id
-product_type_id
-name [...]

product_type

-id
-name [...]

Each table has its own model and relation to each other. An article can have one product assigned. A product has only one product_type assigned.

How can I get all articles with the product_type name "foo"?

$articles = Articles::where('user_id', $userId) ... where product_type.name = 'xyz'

Thanks, Dan

Snapey

8 years ago

Level 122

not sure if whereHas can be nested, so my first approach would be to get the id of type foo

$type=ProductType::where('name','foo')->firstOrFail();

Then use whereHas

$articles = Article::whereHas('products', function ($query) {
    $query->where('product_type_id', $type->id);
})->get();

1 like

dandju

8 years ago

Level 1

This is what I have done in the meantime. But wondering if there is a "more elegant" solution?

There are several products with the same type - so I'm getting all type IDs as an array and using whereIn with whereHas.

Also tried something like

$articles = Article::whereHas('products', function ($query) {
    $query->whereHas('product_type'', function ($query){
        $query->where('name', 'foo');
});
})->get();

Getting product_type method not found.

Snapey

8 years ago

Best Answer

Level 122

'product_type' should be the name of a relationship method on your products model?

dandju

8 years ago

Level 1

That was the error - named the relationship method 'type'.

$articles = Article::where('user_id', $userId)->whereHas('product', function ($query) {
            $query->whereHas('type', function ($query) {
                $query->where('name', 'foo');
            });
        })->get()

Thanks @Snapey

Please or to participate in this conversation.