我想从EAV(实体属性值)表中更详细地提取结果,或者更具体地说,实体元数据表(像wordpress wp_posts和wp_postmeta一样)作为“格式很好的关系表”,以便进行一些排序和/或过滤.
我已经找到了一些如何在查询中格式化结果的示例(而不是编写2个查询并加入代码中的结果),但是我想知道这样做的“最有效”的方法,特别是对于较大的结果集.
当我说“最有效率”时,我的意思是像以下场景:
Get all Entities with last name like XYZ
Return a list of Entities sorted by birthday
例如转过来:
** ENTITY **
-----------------------
ID | NAME | whatever
-----------------------
1 | bob | etc
2 | jane | etc
3 | tom | etc
** META **
------------------------------------
ID | EntityID | KEY | VALUE
------------------------------------
1 | 1 | first name | Bob
2 | 1 | last name | Bobson
3 | 1 | birthday | 1983-10-10
. | 2 | first name | Jane
. | 2 | last name | Janesdotter
. | 2 | birthday | 1983-08-10
. | 3 | first name | Tom
. | 3 | last name | Tomson
. | 3 | birthday | 1980-08-10
进入:
** RESULTS **
-----------------------------------------------
EID | NAME | first name | last name | birthday
-----------------------------------------------
1 | bob | Bob | Bobson | 1983-10-10
2 | jane | Jane | Janesdotter | 1983-08-10
3 | tom | Tom | Tomson | 1980-08-10
所以我可以通过任何meta字段进行排序或过滤.
我发现了一些建议here,但是我找不到有哪些更好的讨论.
选项:
> GROUP_CONCAT:
SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' )
FROM e JOIN m ON e.ID = m.EntityID
ENTITYMETA
>多联:
SELECT e.*, m1.VALUE as 'first name', m2.VALUE as 'last name', m3.VALUE as 'birthday'
FROM e
LEFT JOIN m1
ON e.ID = m1.EntityID AND m1.meta_key = 'first name'
LEFT JOIN m2
ON e.ID = m2.EntityID AND m2.meta_key = 'last name'
LEFT JOIN m3
ON e.ID = m3.EntityID AND m3.meta_key = 'birthday'
ENTITYMETAMETAMETA
>聚结:
SELECT e.*
, MAX( IF(m.KEY= 'first name', m.VALUE, NULL) ) as 'first name'
, MAX( IF(m.KEY= 'last name', m.VALUE, NULL) ) as 'last name'
, MAX( IF(m.KEY= 'birthday', m.VALUE, NULL) ) as 'birthday'
FROM e
JOIN m
ON e.ID = m.EntityID
ENTITYMETA
>代码:
SELECT e.* FROM e WHERE e.ID = {whatever};
ENTITY
在PHP中,从结果创建一个占位符对象
SELECT m.* FROM m WHERE m.EntityID = {whatever};
META
在PHP中,循环遍历结果并附加到实体对象,如:
$e-> {$result-> key} = $result-> VALUE
哪一个更好,一般来说,过滤/排序?
相关问题: