Filter products by options

Question

I have following database structure to store product options.

Now i have problem to filter out products that match only given options. First i did WHERE option_id IN (array of options), but that would give me products that match any of the options and that is not solution. User wants to filter out only products with given material, color, and size for instance. And if i do WHERE option_id = 4 AND option_id = 6 for instance i get nothing.

Here is my query:

SELECT DISTINCT p.id AS id,
                ...
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN product_images pi ON p.id = pi.product_id
LEFT JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1
  AND po.option_id = 1 // only to get the idea
GROUP BY id
ORDER BY id DESC
LIMIT 0,
      12

Just to mention it is PHP application , where user select options from select element with or without multiple attribute.

How to acomplish this?

Answer 1

Thanks guys, i've managed to return exactly what i wanted.

Now i just have problem with pagination query for the filtered products.

Final search query:

SELECT DISTINCT p.id AS id,
                main_price,
                promotion_price,
                NEW,
                sale,
                recommended,
                COUNT(pi.filename) AS image_count,
                GROUP_CONCAT(DISTINCT pi.filename
                             ORDER BY pi.main_image DESC, pi.id ASC) AS images,
                name_sr,
                uri_sr,
                description_sr
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN product_images pi ON p.id = pi.product_id
LEFT JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1
  AND po.option_id IN(1)
  AND p.main_price BETWEEN 5250.00 AND 14000.00
GROUP BY id
HAVING COUNT(DISTINCT po.option_id) = 1
ORDER BY id DESC
LIMIT 0,
      12

Pagination query is something like this i modified it accorgin to new filter query:

SELECT COUNT(DISTINCT p.id) AS number
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN product_images pi ON p.id = pi.product_id
LEFT JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1
  AND po.option_id IN(1)
  AND p.main_price BETWEEN 5250.00 AND 14000.00
GROUP BY(p.id)
HAVING COUNT(DISTINCT po.option_id) = 1

If i leave out DISTINCT in SELECT COUNT i don't get filtered pagination, if i set DISTINCT i get number of rows that corespond to pagination. I suppose i could add another count(*) to all of this with subquery, but not sure if that is way to go and if there is more efficient and elegant way to do this.

Answer 2

You can use having:

SELECT p.id AS id,  ...
FROM products p JOIN
     product_categories pc
     ON p.id = pc.product_id LEFT JOIN
     product_images pi
     ON p.id = pi.product_id JOIN
     product_options po
     ON p.id = po.product_id
WHERE p.product_active = 1 AND
      po.option_id IN (4, 6)
GROUP BY p.id
HAVING COUNT(DISTINCT po.option_id) = 2
ORDER BY p.id DESC
LIMIT 0, 12;

The HAVING clause is specifying that a given id has two matching options. Because of the WHERE clause, these are the only two options that you care about.

I didn't change your approach (you didn't supply the complete query), but you are doing joins along different dimensions -- categories, images, and options. This creates a Cartesian product for each product, and that is often not the best approach to such a query.

Answer 3

There is no need for LEFT JOIN in the solution.

SELECT DISTINCT p.id AS id
FROM products p
JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1
      AND po.option_id IN (1, 2, 3)
GROUP BY p.id
HAVING COUNT(po.option_id) = 3

My solution keep only tables necessary to find the products with specified options.

In the case you want products having exactly this options and no others you can use NOT EXISTS:

SELECT DISTINCT p.id AS id
FROM products p
JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1 AND 
      po.option_id IN (1, 2, 3) and
      NOT EXISTS (
        SELECT 1
        FROM product_options po2
        WHERE p.id = po2.product_id and po2.option_id NOT IN (1, 2, 3)
      )
GROUP BY p.id
HAVING COUNT(po.option_id) = 3

If you want to select products accoding to the other conditions (like product categories and so on) then use IN in the WHERE clause. This approach avoids generating duplicate po.option_id and the outer query will still work correctly even without DISTINCT in COUNT.

SELECT DISTINCT p.id AS id
FROM products p
JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1 AND 
      po.option_id IN (1, 2, 3) AND
      -- use the following IN predicate to select products with specific features without introducing duplicates in your query
      p.id IN (
         select product_id FROM product_categories WHERE <your_condition>
      )
GROUP BY p.id
HAVING COUNT(po.option_id) = 3

Answer 4

You select products with image lists. Something like:

select products.*, group_concat(product_images.id)

Additionally there may be options the product must all meet. This is criteria that belongs in the WHERE clause.

select
  p.*,
  (select group_concat(image) from product_images i where i.product_id = p.id) as images
from products p
where product_active = 1
and id in
(
  select product_id
  from product_options
  where option_id in (1,3,55,97)
  group by product_id
  having count(*) = 4 -- four options in this example
);