我想从EAV(实体-属性-值)表中提取结果,或者更具体地说,实体元数据表(类似于wordpress的wp_posts
和wp_postmeta
),以“漂亮排列的关系表”的形式,以便进行一些排序和/或过滤。
我找到了一些如何在查询内格式化结果的示例(而不是编写2个查询并在代码中连接结果),但我想知道最有效的方法,特别是对于更大的结果集。
当我说“最有效”时,我的意思是适用于以下情况:
获取所有姓氏为XYZ的实体
按生日排序返回实体列表
例如将这样:
** 实体 ** ----------------------- ID | NAME | 其他 ----------------------- 1 | bob | etc 2 | jane | etc 3 | tom | etc
** 元数据 ** ------------------------------------ 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
变成这样:
** 结果 ** ----------------------------------------------- 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
这样我就可以通过任何元字段进行排序或过滤了。
我在这里找到了一些建议(如何旋转EAV),但我找不到任何关于哪个表现更好的讨论。
选项:
- GROUP_CONCAT:
SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' ) FROM `ENTITY` e JOIN `META` m ON e.ID = m.EntityID
- Multi-Join:
SELECT e.*, m1.VALUE as 'first name', m2.VALUE as 'last name', m3.VALUE as 'birthday' FROM `ENTITY` e LEFT JOIN `META` m1 ON e.ID = m1.EntityID AND m1.meta_key = 'first name' LEFT JOIN `META` m2 ON e.ID = m2.EntityID AND m2.meta_key = 'last name' LEFT JOIN `META` m3 ON e.ID = m3.EntityID AND m3.meta_key = 'birthday'
- Coalescing:
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 `ENTITY` e JOIN `META` m ON e.ID = m.EntityID
- Code:
SELECT e.* FROM `ENTITY` e WHERE e.ID = {whatever};
in PHP, create a placeholder object from resultSELECT m.* FROM `META` m WHERE m.EntityID = {whatever};
in PHP, loop through results and attach to entity object like:$e->{$result->key} = $result->VALUE
一般情况下,哪种方法更好,并适用于过滤/排序?
相关问题:
GROUP BY e.ID
。 - ypercubeᵀᴹSELECT e.id, (SELECT m.val FROM meta m WHERE m.key = 'fi' AND m.e_id = e.id ORDER BY m.id LIMIT 1) AS fi, (SELECT n.val FROM meta n WHERE n.key = 'fo' AND n.e_id = e.id ORDER BY n.id LIMIT 1) AS fo FROM entity e
- spencer7593