Laravel: how to filter eloquent data by relationships

Question

I'm building a product catalog in laravel and I would like to filter the products based on their attributes.

I have 2 tables, Products & Attributes. Attributes have a key, value and product_id. For example:

product_id - key   - value
12         - brand - BestBrandEver
23         - brand - EvenBetterBrand

Now I would like to be able to filter down my products via a form on the category page which passes query string parameters like "brand=BestBrandEver or brand=EvenBetterBrand" and retrieve only the products from that brand. Eventually I would like to do the same with color, material etc... It's much like Magento's or Woocommerce layered navigation.

I'm stuck for a while now because I don't know how to start building this the right way. Is there someone who could help me with this or is able to point me in the right direction (maybe even with a tutorial or video)?

Thanks in advance!

Read the docs http://laravel.com/docs/4.2/eloquent#querying-relations or http://softonsofa.com/querying-relations-with-eloquent-in-laravel-4/#straightToThePoint — Jarek Tkaczyk, Dec 01 '14 at 12:28
Dayle Reese's will probably get you up to speed with eloquent relations: http://daylerees.com/codebright/eloquent-queries — the Saint Genius, Dec 01 '14 at 12:54
@SaintGenius Not to depreciate Dayle's work in any way, but what you link is a bit outdated and not really helpful in this case. There's no word about `whereHas` which is what OP needs here. — Jarek Tkaczyk, Dec 01 '14 at 13:36
Not the answer, yes. Not helpful, depends. It's a nice article for new users to get into the eloquent flow, or at least that is what I think. — the Saint Genius, Dec 01 '14 at 13:42
Not to say that I'm an expert, but I know my way around in Eloquent. It's more the thinking behind this problem that bugs me. The logic of solving it. For example, many ecommerce sites have a attribute filters that show how many products with that attribute are left after filtering. For example this dutch site: http://www.schuurmachineshop.nl/category/195321/deltaschuurmachines.html. they have such a attribute filter system and that's what I would want to build. The problem is that I don't know where to start... — Luuk Van Dongen, Dec 01 '14 at 15:09

Joseph Silber · Accepted Answer · 2016-01-20T15:18:15.680

Assuming a url like this:

/products?filters[brand][]=a&filters[brand][]=b&filters[color][]=a&filters[color][]=b

and so forth...

Use the whereHas method to restrict your products by their attributes:

Product::whereHas('attributes', function ($query) {
    foreach (Input::get('filters') as $key => $values) {
        $query->orWhere(function($query) use ($key, $values) {
            $query->where('key', $key)->whereIn('value', $values);
        });
    }
});

Laravel: how to filter eloquent data by relationships

1 Answers1