MySQL get products with multiple filter values

Question

Question:
How would I get all products that are Red and size S? That would mean that I only get product 2: Product (color:green,red => size:S)

Description:
I'm running into a problem with selecting products from a database that have certain filters applied. Below you will find my tables and see SQLfiddle for real SQL.

Table product:

id | title
------------------------------
 1 | Product (color:green => size:S)
 2 | Product (color:green,red => size:S)
 3 | Product (color:red)

Table filter

id | name
------------
 1 | Color
 2 | Size

Table filter_value

id | filter_id | name
---------------------
 1 |         1 | green
 2 |         1 | red
 3 |         2 | S

Table product_filter_value

id | product_id | filter_id | filter_value_id
---------------------------------------------
 1 |          1 |         1 |               1
 2 |          1 |         2 |               3
 3 |          2 |         1 |               1
 4 |          2 |         1 |               2
 5 |          2 |         2 |               3
 6 |          3 |         1 |               2

To select all products with the filter value red, I run this query:

# ALL COLOR RED
SELECT p.*
FROM product p 
  LEFT JOIN product_filter_value pfv ON p.id = pfv.product_id
WHERE (pfv.filter_id IN ('1')) 
  AND (pfv.filter_value_id IN ('2'))
GROUP BY p.id
LIMIT 10 OFFSET 0;

To select all products with the filter value S, I run this query:

# ALL SIZE S
SELECT p.*
FROM product p 
  LEFT JOIN product_filter_value pfv ON p.id = pfv.product_id
WHERE (pfv.filter_id IN ('2')) 
  AND (pfv.filter_value_id IN ('3'))
GROUP BY p.id
LIMIT 10 OFFSET 0;

But how would I get all products that are Red and size S? That would mean that I only get product 2: Product (color:green,red => size:S)

Answer 1

Lets start with the simple basis of looking for one thing. It could be a size, color or anything else your real data has. The query could be

SELECT
      pfv.product_id,
      f.name
   from
      product_filter_value pfv
         join filter_value fv
            ON pfv.filter_value_id = fv.id
              AND fv.name = "what you are looking for"
         JOIN filter f
            ON pfv.filter_id = f.id

Now, you could do multiple for each unique joined in the same query. I will suffix the alias tables for each unique thing being filtered. You could even add a brand if so applicable by an additional subquery join such as ProdBrand alias

SELECT
      p.product_id,
      p.title,
      ProdColor.name as Color,
      ProdSize.name as Size
   from
      Product p
         JOIN ( select 
                      pfv.Product_id,
                      fv.name
                   from 
                      product_filter_value pfv
                         JOIN filter_value fv
                            ON pfv.filter_value_id = fv.id
                           AND fv.name = "red"
                         JOIN filter f
                            ON pfv.filter_id = f.id
                           AND f.name = "Color" ) ProdColor
            ON P.id = ProdColor.Product_ID
         JOIN ( select
                      pfv.Product_id,
                      fv.name
                   from
                      product_filter_value pfv
                        JOIN filter_value fv
                           ON pfv.filter_value_id = fv.id
                             AND fv.name = "S"
                        JOIN filter f
                           ON pfv.filter_id = f.id
                           AND f.name = "Size" ) ProdSize
            ON P.id = ProdSize.Product_ID

Now, if you wanted to try for multiple colors or sizes, just change each subquery's criteria to something like...

 AND INLIST( fv.name, "red", "blue" )  -- example for red OR blue

 AND INLIST( fv.name, "S", "M" )  -- example for Small OR Medium

It may be a long query, but looking at each piece individually is quite simple. Its purpose is the one criteria a color, size, brand, etc. You just are joining on that as part of your overall condition to get products.

I working SQLFiddle from your original schema can be found at this working sample

Answer 2

I can't get the sql fiddle to work, but I thought I'd give this a shot anyway. You might have to have different joins for the different filter types. The join with the table alias pfv1 is for filter1, and pfv2 is for filter 2.

SELECT *
FROM product p 
  LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id and pfv1.filter_id = 1
  LEFT JOIN product_filter_value pfv2 ON p.id = pfv2.product_id and pfv2.filter_id = 2
WHERE (pfv1.filter_id IN ('1')) 
  AND (pfv1.filter_value_id IN ('2'))
  and (pfv2.filter_id IN ('2')) 
  AND (pfv2.filter_value_id IN ('3'))
GROUP BY p.id
LIMIT 10 OFFSET 0;

It may be interesting to look at the whole set without the where clause:

SELECT *
FROM product p 
  LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id and pfv1.filter_id = 1
  LEFT JOIN product_filter_value pfv2 ON p.id = pfv2.product_id and pfv2.filter_id = 2

+ ------- + ----------------------------------- + ------- + --------------- + -------------- + -------------------- + ------- + --------------- + -------------- + -------------------- +
| id      | title                               | id      | product_id      | filter_id      | filter_value_id      | id      | product_id      | filter_id      | filter_value_id      |
| 1       | Product (color:green => size:S)     | 1       | 1               | 1              | 1                    | 2       | 1               | 2              | 3                    |
| 2       | Product (color:green,red => size:S) | 3       | 2               | 1              | 1                    | 5       | 2               | 2              | 3                    |
| 2       | Product (color:green,red => size:S) | 4       | 2               | 1              | 2                    | 5       | 2               | 2              | 3                    |
| 3       | Product (color:red)                 | 6       | 3               | 1              | 2                    |         |                 |                |                      |
+ ------- + ----------------------------------- + ------- + --------------- + -------------- + -------------------- + ------- + --------------- + -------------- + -------------------- +
4 rows

Product 2 has two records because it has two colors, while product 3 has no values for the last four columns because it has no size attribute.

Answer 3

You could get the 2 elements individually, join them and then filter them (in an easy to read word format)

given (and your sql fiddle doesn't load by the way)

/*
drop table if exists product;
create table product(id int ,title varchar(50));
insert into product values
( 1 , 'Product (color:green => size:S)' ),
( 2 , 'Product (color:green,red => size:S)' ),
( 3 , 'Product (color:red)' );

drop table if exists filter;
create Table filter(id int, name varchar(5));
insert into filter values
( 1 , 'Color'),
( 2 , 'Size');

drop table if exists filter_value;
create table filter_value( id int, filter_id int, name varchar(5));
insert into filter_value values
( 1 ,         1 , 'green'),
( 2 ,         1 , 'red'),
( 3 ,         2 , 'S');

drop table if exists product_filter_value;
create Table product_filter_value(id int, product_id int, filter_id int, filter_value_id int);
insert into product_filter_value values
( 1 ,          1 ,         1 ,               1),
( 2 ,          1 ,         2 ,               3),
( 3 ,          2 ,         1 ,               1),
( 4 ,          2 ,         1 ,               2),
( 5 ,          2 ,         2 ,               3),
( 6 ,          3 ,         1 ,               2);
*/

The subquery s gets the colors and the subquery t gets the sizes

select u.stitle product
from
(
select s.* , t.* from 
(
select p.id sid,p.title stitle,fv.name sfvname,f.name sname
from    product p
join  product_filter_value pfv on pfv.product_id = p.id
join    filter_value  fv on fv.id = pfv.filter_value_id
join  filter f on f.id = fv.filter_id
where f.name = 'color'  
) s
join
(
select p.id tid,p.title ttitle,fv.name tfvname,f.name tfname
from    product p
join  product_filter_value pfv on pfv.product_id = p.id
join    filter_value  fv on fv.id = pfv.filter_value_id
join  filter f on f.id = fv.filter_id
where f.name = 'size'
) t on s.stitle = t.ttitle 
) u
where u.sname = 'color' and u.sfvname = 'red' and u.tfname = 'Size' and u.tfvname = 'S'