Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I query a deep relationship in laravel and filter parents by children?

For example, if a category has many products which has many skus, how do I get all products that have a sku with a price greater than 10?

This returns all the categories but only has the expected skus attached, where I want only the categories that have said skus.

$category = new Category();
$category->with(array('products', 'products.skus' => function ($query) {
    $query->where('price', '>', 10);
}))->get();
like image 272
Aaron Campbell Avatar asked Jan 23 '15 20:01

Aaron Campbell


1 Answers

What you're looking for is whereHas(). Also you can write with(array('products.skus')) directly without products

$category = new Category();
$categories = $category->with(array('products', 'products.skus' => function ($query) {
        $query->where('price', '>', 10);
    }))
    ->whereHas('products.skus', function($query){
        $query->where('price', '>', 10);
    })->get();

You need both, with and whereHas but you can simplify the code a bit by putting the closure in a variable:

$priceGreaterTen = function($query){
    $query->where('price', '>', 10);
};

$category = new Category();
$categories = $category->with(array('products', 'products.skus' => $priceGreaterTen))
                       ->whereHas('products.skus', $priceGreaterTen)
                       ->get();
like image 83
lukasgeiter Avatar answered Oct 20 '22 18:10

lukasgeiter