mysql - 同一列不同行的 WHERE AND 子句

Question

事先信息通知

我有 3 个表:

类型

+----+-------------+-----------------------+------------+------------+
| id | category_id | name                  | created_at | updated_at |
+----+-------------+-----------------------+------------+------------+
|  1 |           1 | T-Shirts              | NULL       | NULL       |
+----+-------------+-----------------------+------------+------------+

原型(prototype)

+----+-----------------------------------------+------------+------------+
| id | name                                    | created_at | updated_at |
+----+-----------------------------------------+------------+------------+
|  1 | Gildan Softstyle Adult Ringspun T-shirt | NULL       | NULL       |
+----+-----------------------------------------+------------+------------+

过滤器

+----+-------------+---------------------+-------+------------+------------+
| id | name        | value               | extra | created_at | updated_at |
+----+-------------+---------------------+-------+------------+------------+
|  1 | gender      | male                | NULL  | NULL       | NULL       |
|  2 | gender      | female              | NULL  | NULL       | NULL       |
|  3 | age_group   | adult               | NULL  | NULL       | NULL       |
|  4 | age_group   | child               | NULL  | NULL       | NULL       |
|  5 | age_group   | baby                | NULL  | NULL       | NULL       |
+----+-------------+---------------------+-------+------------+------------+

它们通过n-m关系相互关联，因此也有各自的联结表types_prototypes、types_filters、prototypes_filters。有关更多详细信息，请同时查看我的 dump file .

问题本身

我正在尝试设置过滤系统(使用 Laravel)，所以我需要查询与所有给定的过滤器相关的所有原型(prototype)(逻辑和)。到现在为止我已经搞定了，只要使用只选择一个Filter:

select * from `prototypes` 
inner join `types_prototypes` on `prototypes`.`id` = `types_prototypes`.`prototype_id` 
inner join `prototypes_filters` on `prototypes`.`id` = `prototypes_filters`.`prototype_id` 
inner join `filters` on `prototypes_filters`.`filter_id` = `filters`.`id` 
where `types_prototypes`.`type_id` = ? and `filter_id` = ? group by `prototypes`.`id`

问题本身在于这个查询是不适用的，只要我们有几个过滤器应该同时有效:

...
where `types_prototypes`.`type_id` = ? and `filter_id` = ? and `filter_id` = ? group by `prototypes`.`id`

我知道，where ... and 不起作用，因为我只需要加入一列 filter_id，它同时只能包含一个值时间(实际上 groupBy() 负责)。所以从这个意义上说，我有一个新的一行来表示同一 Prototype 与另一个 Filter 的关系，例如:

+----+-----------------------------------------+------------+------------+---------+--------------+--------------+-----------+----+-----------+-------+-------+------------+------------+
| id | name                                    | created_at | updated_at | type_id | prototype_id | prototype_id | filter_id | id | name      | value | extra | created_at | updated_at |
+----+-----------------------------------------+------------+------------+---------+--------------+--------------+-----------+----+-----------+-------+-------+------------+------------+
|  1 | Gildan Softstyle Adult Ringspun T-shirt | NULL       | NULL       |       1 |            1 |            1 |         1 |  1 | gender    | male  | NULL  | NULL       | NULL       |
|  1 | Gildan Softstyle Adult Ringspun T-shirt | NULL       | NULL       |       1 |            1 |            1 |         3 |  3 | age_group | adult | NULL  | NULL       | NULL       |
+----+-----------------------------------------+------------+------------+---------+--------------+--------------+-----------+----+-----------+-------+-------+------------+------------+

我已经尝试了几种不同的方法，包括where 'filter_id' in(?,?)、where FIND_IN_SET('filter_id', '?,?')甚至根据 EAV 模式 重组我的数据库(当 filters 分为 filter_names 和 filter_values 时) .但是每次我只获得满足整个集合的一个要求的条目(等于逻辑OR)，例如(这里我们有成人 和的原型(prototype)strong>男人，但不仅成年男人):

+----+-----------------------------------------+------------+------------+---------+--------------+--------------+-----------+----+-----------+-------+-------+------------+------------+
| id | name                                    | created_at | updated_at | type_id | prototype_id | prototype_id | filter_id | id | name      | value | extra | created_at | updated_at |
+----+-----------------------------------------+------------+------------+---------+--------------+--------------+-----------+----+-----------+-------+-------+------------+------------+
|  1 | Gildan Softstyle Adult Ringspun T-shirt | NULL       | NULL       |       1 |            1 |            1 |         1 |  1 | gender    | male  | NULL  | NULL       | NULL       |
|  2 | American Apparel Womans T-Shirt         | NULL       | NULL       |       1 |            2 |            2 |         3 |  3 | age_group | adult | NULL  | NULL       | NULL       |
|  3 | Gildan Adult Cotton T-shirt             | NULL       | NULL       |       1 |            3 |            3 |         1 |  1 | gender    | male  | NULL  | NULL       | NULL       |
|  4 | American Apparel Mens T-Shirt           | NULL       | NULL       |       1 |            4 |            4 |         1 |  1 | gender    | male  | NULL  | NULL       | NULL       |
|  5 | American Apparel Kids T-Shirt           | NULL       | NULL       |       1 |            5 |            5 |         1 |  1 | gender    | male  | NULL  | NULL       | NULL       |
+----+-----------------------------------------+------------+------------+---------+--------------+--------------+-----------+----+-----------+-------+-------+------------+------------+

我快绝望了，有人知道吗？感谢您的建议，抱歉写了这么多文字，我只是想描述所有情况。

Answer 1

您必须针对每个条件重复加入过滤器表。

select * from prototypes AS p

inner join types_prototypes AS tp1 on p.id = tp1.prototype_id 
inner join prototypes_filters AS pf1 on p.id = pf1.prototype_id 
inner join filters AS f1 on pf1.filter_id = f1.id

inner join types_prototypes AS tp2 on p.id = tp2.prototype_id 
inner join prototypes_filters AS pf2 on p.id = pf2.prototype_id 
inner join filters AS f2 on pf2.filter_id = f2.id

where tp1.type_id = ? and f1.filter_id = ? 
AND tp2.type_id = ? and f2.filter_id = ?
group by prototypes.id