php - 管理未知数量 EAV 属性上的 JOIN

Question

给定一个名为 item 的表，其中包含“主”记录，例如:

item_id    item_type    item_name    item_description
1          clothing     T-Shirt      Get your t-shirt here
2          clothing     Polo Shirt   Another kind of short-sleeve shirt
3          computer     Macbook      2016 Macbook Pro with retina

另一个名为 custom_fields 的表，我认为它具有 EAV 结构(实体-属性-值):

id       item_id     attribute    value
1        1           size         large
2        1           color         purple
3        2           size          medium
4        2           color         green
5        3           memory        16GB
6        3           hard drive    512GB SSD

编写包含所有指定/配置属性并且还支持ORDER BY的查询的最佳方法是什么？

事实:
1. 数据结构不能改变。
2. 属性未知(用户配置)。3. 从技术上讲，可以为给定类型的项目设置无限数量的“属性”(例如上面的 size 或 color)(实际上，我可以'想象不到超过 50 个)。
4. 哪些属性包含/不包含在查询中是未知的(用户配置的)。

假设指定的属性存在于数组中，如:

$attributes = ['size', 'color'];

似乎有些 PHP 会执行以下操作:

$counter = 1;
$from = 'item AS i';
$fields = 'name, description';
$order_by = 'size';

foreach( $attributes AS $attribute ) {
    $table_alias = "a{$counter}";
    $fields.= ",{$table_alias}.value AS {$attribute}";
    $from.= " INNER JOIN custom_fields AS {$table_alias} ON i.item_id = {$table_alias}.item_id AND {$table_alias}.attribute = '{$attribute}'";
    $counter++;
}   

$query = "SELECT {$fields} FROM {$from} {$where} ORDER BY {$order_by}";

将构造一个可用于包含数据并允许 ORDER BY 的查询。该查询看起来像:

SELECT name, description, a1.value AS size, a2.value AS color 
    FROM item AS i 
    INNER JOIN custom_fields AS a1 ON i.item_id = a1.item_id 
        AND a1.attribute = 'size'
    INNER JOIN custom_fields AS a2 ON i.item_id = a2.item_id 
        AND a2.attribute = 'color'

但是，如果有 20 多个列，我担心这个查询看起来很令人讨厌(20 JOINS)并且可能存在性能问题。

有更好的方法来构建它吗？是否有一些方法(例如使用临时表)可能更有用或更高效？

更新:
无法确保 MySQL 的版本是特定的，因此应该与任何版本兼容，因此请假设版本 5.0。

Answer 1

您也可以仅使用一个 **JOIN* 来完成此操作，如下所示:

SELECT m.*
    ,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
    ,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
    ,GROUP_CONCAT(IF(cf.attribute = 'memory', cf.`value`, NULL)) AS 'memory'
    ,GROUP_CONCAT(IF(cf.attribute = 'hard drive', cf.`value`, NULL)) AS 'hard drive'
FROM master m
LEFT JOIN custom_fields cf ON m.item_id = cf.item_id
GROUP BY m.item_id;

要生成产品(或更多)的查询，您可以使用以下命令:对于所有产品，请删除 (1) 中的行 WHERE item_id;

选择 m.*

select DISTINCT
 CONCAT("SELECT m.*\n"
 , GROUP_CONCAT(",GROUP_CONCAT(IF(cf.attribute = '",attribute,"', cf.`value`, NULL)) AS '",attribute,"'" SEPARATOR '\n')
 , "FROM master m\nLEFT JOIN custom_fields cf ON m.item_id = cf.item_id\nGROUP BY m.item_id") as myquery
FROM custom_fields
WHERE item_id in (1);

示例

mysql> select DISTINCT
    ->  CONCAT("SELECT m.*\n"
    ->  , GROUP_CONCAT(",GROUP_CONCAT(IF(cf.attribute = '",attribute,"', cf.`value`, NULL)) AS '",attribute,"'" SEPARATOR '\n')
    ->  , "FROM master m\nLEFT JOIN custom_fields cf ON m.item_id = cf.item_id\nGROUP BY m.item_id") as myquery
    -> FROM custom_fields;

结果

    SELECT m.*
,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
,GROUP_CONCAT(IF(cf.attribute = 'memory', cf.`value`, NULL)) AS 'memory'
,GROUP_CONCAT(IF(cf.attribute = 'hard drive', cf.`value`, NULL)) AS 'hard drive'FROM master m
LEFT JOIN custom_fields cf ON m.item_id = cf.item_id
GROUP BY m.item_id 

1 row in set (0,00 sec)

mysql>

并执行此查询

mysql>  SELECT m.*
    -> ,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
    -> ,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
    -> ,GROUP_CONCAT(IF(cf.attribute = 'size', cf.`value`, NULL)) AS 'size'
    -> ,GROUP_CONCAT(IF(cf.attribute = 'color', cf.`value`, NULL)) AS 'color'
    -> ,GROUP_CONCAT(IF(cf.attribute = 'memory', cf.`value`, NULL)) AS 'memory'
    -> ,GROUP_CONCAT(IF(cf.attribute = 'hard drive', cf.`value`, NULL)) AS 'hard drive'FROM master m
    -> LEFT JOIN custom_fields cf ON m.item_id = cf.item_id
    -> GROUP BY m.item_id ;
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
| item_id | item_type | item_name  | item_description                   | size   | color  | size   | color  | memory | hard drive |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
|       1 | clothing  | T-Shirt    | Get your t-shirt here              | large  | purple | large  | purple | NULL   | NULL       |
|       2 | clothing  | Polo Shirt | Another kind of short-sleeve shirt | medium | green  | medium | green  | NULL   | NULL       |
|       3 | computer  | macbook    | 2016 Macbook Pro with retina       | NULL   | NULL   | NULL   | NULL   | 16GB   | 512GB SSD  |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
3 rows in set (0,00 sec)

mysql>

带有准备声明的示例

mysql> select DISTINCT
    ->  CONCAT("SELECT m.*\n"
    ->  , GROUP_CONCAT(",GROUP_CONCAT(IF(cf.attribute = '",attribute,"', cf.`value`, NULL)) AS '",attribute,"'" SEPARATOR '\n')
    ->  , "FROM master m\nLEFT JOIN custom_fields cf ON m.item_id = cf.item_id\nGROUP BY m.item_id") as myquery INTO @sql
    -> FROM custom_fields;
Query OK, 1 row affected (0,00 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0,01 sec)
Statement prepared

mysql> execute stmt;
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
| item_id | item_type | item_name  | item_description                   | size   | color  | size   | color  | memory | hard drive |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
|       1 | clothing  | T-Shirt    | Get your t-shirt here              | large  | purple | large  | purple | NULL   | NULL       |
|       2 | clothing  | Polo Shirt | Another kind of short-sleeve shirt | medium | green  | medium | green  | NULL   | NULL       |
|       3 | computer  | macbook    | 2016 Macbook Pro with retina       | NULL   | NULL   | NULL   | NULL   | 16GB   | 512GB SSD  |
+---------+-----------+------------+------------------------------------+--------+--------+--------+--------+--------+------------+
3 rows in set (0,00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0,00 sec)

mysql>