steady inlet
Hey,
I was wondering if it's possible to query products by multiple categories and only get those products that have all these categories? Currently, when I query with multiple category ids I'm getting all products that have at least one of the categories specified.
shell rapids
Hey @steady inlet , this is not currently possible since we query the products with category IDs in an IN condition.
Can you describe your use-case here so that we can make a case for bringing this into the core?
steady inlet
Thanks @shell rapids for the prompt answer. Meanwhile I've been looking into the code and found the place where the IN condition is built. I am not the real sql expert here. Getting what is desired via a custom endpoint/ service, how would such a query look like, ideally?
The use case is simple. I am working on a shop that deals with new, but also a lot with refurbished products. Those products undergo a visual and technical rating, something like "New", "Like new", etc. Given other categories for product types and/or functions (Part-X, Part-Y) it would come in handy to be able to filter by both values:
shell rapids
Thanks @steady inlet , can you also share what your categories structure looks like? Correct me if I'm wrong, but these seem like product attributes (New or Like New) that can be filtered upon on top of categories (mobiles, laptops).
Either way, you'd have to write custom code to achieve the AND clause. The SQL can look like this:
SELECT p.*
FROM products p
JOIN product_category_product pcp ON p.id = pcp.product_id
WHERE pcp.category_id = category_id_1 AND pcp.category_id = category_id_2;
It is not yet fully thought through and therefore there is not yet a complete category tree either. I just started testing things out what possibilities/ advantages the new api might have. Basically what we're running with now is a mixture of collections, types and tags to organise the product catalogue. I kind of find this approach sub-optimal since we're forced to use different concepts to express the same idea. In my opinion, it should not be the question of the exact semantics of a given category, attribute, type etc. As far as I understand, one of the main reasons to introduce product categories was to provide better/ simplified ways to both organise and filter products in admin and storefront. While the approach I am trying to explore here might be a bit special, I don't think it's very unusual.
shell rapids
I understand. We would have to wait for more feedback to see if this ability needs to be added to the core. Thanks for sharing the use-case, it was helpful.