Mysql WHERE 不适用于具有多个联接的表

Question

我正在为多个实体(产品、公司……)开发 EAV 系统我会在最后告诉你问题。

数据库结构如下:

EAV_ENTITY

此表将为我提供所有可用实体的列表，例如:

1 | Product
2 | Company

结构:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| model | varchar(255)     | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

EAV_ATTRIBUTE

该表有一个属性列表，ref是助记符，可以更轻松地通过查询获取属性。我在此处添加与表 eav_entity 相关的entity_type_id，因为我想将每个属性限制为只有一个实体。

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| entity_type_id | int(11) unsigned | NO   | MUL | NULL    |                |
| ref            | varchar(50)      | NO   | UNI |         |                |
| name           | varchar(255)     | NO   | UNI |         |                |
+----------------+------------------+------+-----+---------+----------------+

EAV_VALUE

该表具有与属性相关的值，对于属性“颜色”，值将是:红色、绿色、橙色

+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| attribute_id    | int(11) unsigned | NO   | MUL | NULL    |                |
| value           | varchar(255)     | NO   | MUL | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

EAV_ATTRIBUTE_VALUE

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| entity_id      | int(11) unsigned | YES  |     | NULL    |                |
| entity_type_id | int(11) unsigned | YES  | MUL | NULL    |                |
| value_id       | int(11)          | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

在这里，我实现了所有的“魔法”，我进行了一个查询，在实体(类型、id)和所有相关值以及该值所属的属性之间建立关系。

我的查询是这个

SELECT
    e.model,
    av.entity_id,
    a.id as category_id,
    a.ref as category_ref,
    v.id as value_id

FROM eav_entity as e

RIGHT JOIN eav_attribute_value as av
ON e.id = av.entity_type_id

LEFT JOIN eav_value as v
ON v.id = av.value_id

LEFT JOIN eav_attribute as a
ON v.attribute_id = a.id

WHERE e.model = 'App\Product'

问题与问题

当我添加语句 WHERE e.model ='Whatever' 时，我没有得到任何结果。而且应该是过滤器。但如果没有 WHERE 语句，我会得到预期的结果。我认为这可能是与我进行连接的方式有关的问题。

没有 WHERE e.model 的真实示例

+-------------+-----------+-------------+---------------------------+----------+
| model       | entity_id | category_id | category_ref              | value_id |
+-------------+-----------+-------------+---------------------------+----------+
| App\Product |        13 |           2 | product_development_phase |        2 |
| App\Product |        13 |           2 | product_development_phase |        3 |
| App\Product |        13 |           4 | product_therapeutics      |       58 |
| App\Company |        13 |           4 | product_therapeutics      |       58 |
+-------------+-----------+-------------+---------------------------+----------+

Answer 1

正如 @Uuerdo 所说，我需要添加第二个反斜杠作为转义字符，以将第二个反斜杠解释为真正的反斜杠。

查询将产生以下结果:

SELECT
    e.model,
    av.entity_id,
    a.id as category_id,
    a.ref as category_ref,
    v.id as value_id

FROM eav_entity as e

RIGHT JOIN eav_attribute_value as av
    ON e.id = av.entity_type_id

LEFT JOIN eav_value as v
    ON v.id = av.value_id

LEFT JOIN eav_attribute as a
    ON v.attribute_id = a.id

WHERE e.model = "App\\Product"