MySQL通过多个数据透视表查询WHERE

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 |
+----+------------+-------------+

我需要查询我的数据库，以便它选择 products，这些 products 具有附加到产品本身的某些 properties OR 将这些属性附加到一个其相关的 product_variants。具有相同 properties.property_key_id 的 properties 也应该像这样分组:(pkey1='red' OR pkey1='blue') AND (pkey2='fruit' OR pkey2='vegetable' )

案例:

• 选择带有 (color='red' AND type='vegetable') 的所有产品。这应该只返回 Tomato。
• 选择所有带有 ((color='red' OR color='yellow') AND type='fruit') 的产品应该返回苹果和香蕉

请注意，在上面的示例中，我实际上不需要通过 properties.value 进行查询，我可以通过 properties.id 进行查询。

我经常使用 MySQL 查询，但我遇到的最大问题是通过两个数据透视表加载的属性。加载它们没有问题，但加载它们并将它们与正确的 WHERE、AND 和 OR 语句组合起来是问题。

Answer 1

以下代码应为您提供您要查找的内容，但您应注意，您的餐 table 当前有一个列为黄色的番茄和一个蔬菜。显然，您希望番茄是红色的，而番茄实际上是水果而不是蔬菜:

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'

这里是 SQL Fiddle:http://www.sqlfiddle.com/#!9/309ad/3/0