MySQL Query WHERE through multiple pivot tables

Question

products 
+----+--------+
| id | title  |
+----+--------+
|  1 | Apple  |
|  2 | Pear   |
|  3 | Banana |
|  4 | Tomato |
+----+--------+

product_variants
+----+------------+------------+
| id | product_id | is_default |
+----+------------+------------+
|  1 |          1 |          0 |
|  2 |          1 |          1 |
|  3 |          2 |          1 |
|  4 |          3 |          1 |
|  5 |          4 |          1 |
+----+------------+------------+

properties
+----+-----------------+-----------+
| id | property_key_id |   value   |
+----+-----------------+-----------+
|  1 |               1 | Yellow    |
|  2 |               1 | Green     |
|  3 |               1 | Red       |
|  4 |               2 | Fruit     |
|  5 |               2 | Vegetable |
|  6 |               1 | Blue      |
+----+-----------------+-----------+

property_keys
+----+-------+
| id | value |
+----+-------+
|  1 | Color |
|  2 | Type  |
+----+-------+

product_has_properties
+----+------------+-------------+
| id | product_id | property_id |
+----+------------+-------------+
|  1 |          1 |           4 |
|  2 |          1 |           3 |
|  3 |          2 |           4 |
|  4 |          3 |           4 |
|  5 |          3 |           4 |
|  6 |          4 |           4 |
|  7 |          4 |           5 |
+----+------------+-------------+

product_variant_has_properties
+----+------------+-------------+
| id | variant_id | property_id |
+----+------------+-------------+
|  1 |          1 |           2 |
|  2 |          1 |           3 |
|  3 |          2 |           6 |
|  4 |          3 |           4 |
|  5 |          4 |           1 |
|  6 |          5 |           1 |
+----+------------+-------------+

I need to query my DB so it selects products which have certain properties attached to the product itself OR have those properties attached to one of its related product_variants. Also should properties with the same properties.property_key_id be grouped like this: (pkey1='red' OR pkey1='blue') AND (pkey2='fruit' OR pkey2='vegetable')

Example cases:

• Select all products with (color='red' AND type='vegetable'). This should return only Tomato.
• Select all products with ((color='red' OR color='yellow') AND type='fruit') should return Apple and Banana

Please note that in the example cases above I don't really need to query by properties.value, I can query by properties.id.

I played around a lot with MySQL query's but the biggest problem I'm struggling with is the properties being loaded through two pivot tables. Loading them is no problem but loading them and combining them with the correct WHERE, AND and OR statements is.

Answer 1

The following code should give you what you're looking for, however you should note that your table currently has a Tomato listed as yellow and a vegetable. Obviously you want the Tomato as red and a Tomato is actually a fruit not a vegetable:

Select distinct title 
from products p
inner join
product_variants pv on pv.product_id = p.id
inner join
product_variant_has_properties pvp on pvp.variant_id = pv.id
inner join
product_has_properties php on php.product_id = p.id
inner join
properties ps1 on ps1.id = pvp.property_id --Color
inner join
properties ps2 on ps2.id = php.property_id --Type
inner join
property_keys pk on pk.id = ps1.property_key_id or pk.id = ps2.property_key_id

where ps1.value = 'Red' and ps2.value = 'Vegetable'

Here is the SQL Fiddle: http://www.sqlfiddle.com/#!9/309ad/3/0