mysql - 一个查询从 A 获取，将 B 的字段值连接为 A 中的行字段，然后过滤和排序？

Question

为了创建易于重用和配置的 CMS，我在数据库中创建了下表。我可以创建一个包含任意数量的任意命名属性的 PHP 对象数组，并且使用此结构可以保存它，而无需为每种对象类型创建表格。有点像 Wordpress 对每个自定义项目类型使用“帖子”表。

struct (id, type)

1, 'article'
2, 'article'
3, 'employee'
4, 'article'
...


struct_fields (id, struct_id, sub_id, type, val)

1, 1, 'title', 'string', 'Great article'
2, 1, 'publish_date', 'datetime', '2019-03-02 21:00:00'
3, 1, 'active', 'boolean', 1
4, 1, 'body', 'wysiwyg', '<p>Contents</p>'

5, 2, 'title', 'string', 'Another fantastic article'
6, 2, 'publish_date', 'datetime', '2018-03-01 14:32:17'
7, 2, 'active', 'boolean', 1    
8, 2, 'body', 'wysiwyg', '<p>Cool contents</p>'

9, 3, 'firstname', 'string', 'Jos'
10, 3, 'lastname', 'string', 'Fabre'

11, 4, 'title', 'string', 'This is amazing'
12, 4, 'publish_date', 'datetime', '2018-03-01 19:21:34'
13, 4, 'active', 'boolean', 0   
14, 4, 'body', 'wysiwyg', '<p>Bad content!</p>'
...

我正在努力创建一个查询，该查询可以取出一系列结构及其相关字段作为属性。我不确定这是否可能。我想对属性(因此相关表中的字段)进行排序

例如我想创建一个查询，可以获取发布日期为过去的最新 3 篇活跃文章。我已经在 J​​OIN 和 GROUP BY 上挣扎了好几天了。对不同类型结构的查询可以按特定字段排序，或按另一个字段过滤，或两者兼而有之。

所以，例如对于文章，我希望输出如下:

[id, active, publish_date, title, body]

然后根据事件进行过滤并根据发布日期进行排序。 id 将来自 struct 表，其余字段来自 struct_fields 表中的连接字段。

对于员工来说应该是这样的:

[id, firstname, lastname] 

然后按姓氏 ASC、名字 ASC 排序

在一种情况下，我能够使用 MAX 将 struct_field 属性获取到结果行字段中，但无法在没有 SQL 错误的情况下对它们进行排序

SELECT  struct.id, struct.type, struct.published,
        MAX(CASE WHEN struct_fields.sub_id = 'active' THEN struct_fields.vc1 ELSE NULL END) AS active,
        MAX(CASE WHEN struct_fields.sub_id = 'title' THEN struct_fields.vc1 ELSE NULL END) AS title,
        MAX(CASE WHEN struct_fields.sub_id = 'body' THEN struct_fields.tx1 ELSE NULL END) AS body,
        MAX(CASE WHEN struct_fields.sub_id = 'allday' THEN struct_fields.vc1 ELSE NULL END) AS allday,
        MAX(CASE WHEN struct_fields.sub_id = 'start_datetime' THEN struct_fields.vc1 ELSE NULL END) AS start_datetime,
        MAX(CASE WHEN struct_fields.sub_id = 'end_datetime' THEN struct_fields.vc1 ELSE NULL END) AS end_datetime
FROM struct
INNER JOIN struct_fields ON (struct_fields.struct_id = struct.id)
WHERE struct.type = 'events'
GROUP BY struct.id

救命啊!这甚至可以在一个查询中完成吗？

更新:@Nic3500 suggested发布创建 sql 和一些示例插入，所以它们是:https://pastebin.com/raw/0hPYByDq

Answer 1

如果您使用别名进行排序，则可能会发生这种情况

但您也可以使用列位置，例如:

  SELECT  struct.id, struct.type, struct.published,
          MAX(CASE WHEN struct_fields.sub_id = 'active' THEN struct_fields.vc1 ELSE NULL END) AS active,
          MAX(CASE WHEN struct_fields.sub_id = 'title' THEN struct_fields.vc1 ELSE NULL END) AS title,
          MAX(CASE WHEN struct_fields.sub_id = 'body' THEN struct_fields.tx1 ELSE NULL END) AS body,
          MAX(CASE WHEN struct_fields.sub_id = 'allday' THEN struct_fields.vc1 ELSE NULL END) AS allday,
          MAX(CASE WHEN struct_fields.sub_id = 'start_datetime' THEN struct_fields.vc1 ELSE NULL END) AS start_datetime,
          MAX(CASE WHEN struct_fields.sub_id = 'end_datetime' THEN struct_fields.vc1 ELSE NULL END) AS end_datetime
  FROM struct
  INNER JOIN struct_fields ON (struct_fields.struct_id = struct.id)
  WHERE struct.type = 'events'
  GROUP BY struct.id
  order 1,2,3  

通过这种方式，您应该更加独立，您可以按

为您的订单构建动态范围或设置