php - 如何从选定字段列表构建 SQL 查询？

Question

我有一个使用 PHP 和 MySQL 编写后端程序的应用程序。我的大部分表都是标准的 MySQL Innodb 表。此外，我还有一个功能，管理员可以使用 EAV model 添加自定义属性。

我需要在应用程序中构建一个用户可以创建自定义 View 的功能。基本上，我想为用户提供所有可用字段的列表。用户可以选择他/她想在他/她的屏幕上看到的字段，系统将使用适当的 JOINS 创建查询并显示结果。

例如，如果用户希望显示字段 1、2、3、4、5、6、7、8、9。我将需要执行以下步骤

1. 我会检查每个字段属于哪个表。
2. 找出哪个表名进入查询的FROM部分
3. 如果需要，找出查询的 JOIN 部分中的表名。并确定字段何时进入 JOIN 语句的 ON 子句。

第一步很容易做到，因为它存储在 entity_definitions 表“定义如下”

第二步也相当简单，因为我知道哪个表是基表，所以我可以填写它(即 accounts)。

“如果需要”第三步非常复杂，我不确定如何完成。我怎么知道要为 Join 使用哪个表以及要为 ON 子句使用什么列名？

我试图通过创建一个包含 2 列的表来解决该问题，一列用于字段名称，另一列用于外部字段名称。但是，我仍然无法仅通过查看所选字段名称来构建查询。

为了跟踪表/属性定义，我创建了一个表(即 entity_definitions)，其中包含所有列的列表。

CREATE TABLE entity_definitions (
  entity_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  display_name varchar(255) NOT NULL,
  entity_key varchar(60) NOT NULL,
  entity_type enum('lookup','Integer','text','datetime','date') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  object_id int(11) unsigned NOT NULL,
  PRIMARY KEY (entity_id),
  KEY object_id (object_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

object_id 引用它在 entity_objects 表中所属的表名。

CREATE TABLE entity_objects (
  object_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  object_name varchar(60) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  object_description varchar(255) DEFAULT NULL,
  PRIMARY KEY (object_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

这是一个jFiddle link其中包含我目前已完成的表格和一些数据/查询。

问题:如何完成第 3 步？也欢迎更好的想法。

Answer 1

考虑以下...尽管请注意最好在附件表上进行任何进一步的过滤，因为这些表上的索引对 super 查询不可用...

SELECT * from eav_core;
+----+--------+------------+----------+
| id | entity | attribute  | datatype |
+----+--------+------------+----------+
|  1 |      1 | start_date | DATE     |
|  2 |      1 | shift      | INTEGER  |
|  3 |      1 | station1   | VARCHAR  |
|  4 |      1 | station2   | VARCHAR  |
|  5 |      1 | station3   | VARCHAR  |
|  6 |      2 | start_date | DATE     |
|  7 |      2 | shift      | INTEGER  |
|  8 |      2 | station1   | VARCHAR  |
| 11 |      1 | end_date   | date     |
| 12 |      2 | end_date   | date     |
+----+--------+------------+----------+

SELECT * from eav_strings;
+--------+-----------+-------+
| entity | attribute | value | -- value = VARCHAR(50)
+--------+-----------+-------+
|      1 | station1  | Mark  |
|      1 | station2  | John  |
|      1 | station3  | Macy  |
|      2 | station1  | John  |
+--------+-----------+-------+

SELECT * from eav_dates;
+--------+------------+------------+
| entity | attribute  | value      | -- value = DATE
+--------+------------+------------+
|      1 | end_date   | 2015-05-09 |
|      1 | start_date | 2009-04-03 |
|      2 | end_date   | 2015-07-14 |
|      2 | start_date | 2009-04-04 |
+--------+------------+------------+

SELECT * from eav_integers;
+--------+-----------+-------+
| entity | attribute | value | -- value = INT
+--------+-----------+-------+
|      1 | shift     |     1 |
|      2 | shift     |     2 |
+--------+-----------+-------+

SELECT entity
     , MAX(CASE WHEN attribute = 'start_date' THEN value END) start_date
     , MAX(CASE WHEN attribute = 'end_date' THEN value END) end_date
     , MAX(CASE WHEN attribute = 'station1' THEN value END) station1
     , MAX(CASE WHEN attribute = 'station2' THEN value END) station2
     , MAX(CASE WHEN attribute = 'station3' THEN value END) station3
     , MAX(CASE WHEN attribute = 'shift' THEN value END) shift
  FROM
     (
       SELECT x.entity
            , x.attribute
            , y.value 
         FROM eav_core x 
         JOIN eav_dates y
           ON y.entity = x.entity 
          AND y.attribute = x.attribute 
        WHERE x.datatype = 'date'
        UNION ALL
       SELECT x.entity
            , x.attribute
            , y.value 
         FROM eav_core x 
         JOIN eav_integers y 
           ON y.entity = x.entity 
          AND y.attribute = x.attribute 
        WHERE x.datatype = 'integer'
        UNION ALL
       SELECT x.entity
            , x.attribute
            , y.value 
         FROM eav_core x 
         JOIN eav_strings y 
           ON y.entity = x.entity 
          AND y.attribute = x.attribute 
        WHERE x.datatype = 'varchar'
     ) n
 GROUP
    BY entity;
+--------+------------+------------+----------+----------+----------+-------+
| entity | start_date | end_date   | station1 | station2 | station3 | shift |
+--------+------------+------------+----------+----------+----------+-------+
|      1 | 2009-04-03 | 2015-05-09 | Mark     | John     | Macy     | 1     |
|      2 | 2009-04-04 | 2015-07-14 | John     | NULL     | NULL     | 2     |
+--------+------------+------------+----------+----------+----------+-------+