MySQL EAV SELECT一对多单行结果
我在这里看到过类似的问题,但一直没有找到符合我的具体情况的问题。我有以下三个表:MySQL EAV SELECT一对多单行结果
content
id, [other fields]
attributes
id, title
attributes_values
id, attribute_id[foreign to attributes.id], content_id[foreign to content.id], value
我想是一个单一的查询(或子查询的组),可以返回适当的数据,以避免在编程操作这一点。
有关表格的更多信息:属性有三个记录(型号#,零件号和购物车显示)。
我需要选择内容中的所有记录,其中,在购物车显示的attribute_value = 1。与此同时,我想将其他相关的attribute_values作为其相关的attribute.title返回。结果会是这个样子:
id [other fields] Model # [from attributes.title field] Part # [from attributes.title field]
1 [any data] value from attributes_values value from attributes_values
2 [any data] value from attributes_values value from attributes_value
到目前为止,我唯一实现这一点的方式是不是很优雅,因为我不必做多加入到同一个表:
SELECT * FROM content AS a
LEFT JOIN attributes_values AS b ON (b.content_id = a.id AND b.attribute_id = [Model # ID])
LEFT JOIN attributes_values AS c ON (c.content_id = a.id AND c.attribute_id = [Part # ID])
WHERE a.id IN (
SELECT content_id FROM attributes_values WHERE attribute_id = [Show in Cart ID] AND value = 1
)
由于你可以看到,我不得不将相关的键硬编码到JOIN语句中,这不会使其具有很高的可伸缩性(并且感觉很脏)。
一些最后的几个笔记:我使用PHP和Joomla !,所以如果这会影响您的答案(或者如果有一些秘密的Joomla提示),请随时加入。此外,这是一个预先建立的表格模式,我无法改变,所以我需要上述表格的查询解决方案,而不是更好的表格设计的建议。
任何帮助将不胜感激。
干杯!
而是加入你的表多次为每个属性的,你可以结合使用像MAX()的聚集函数的案件时声明:
SELECT
content.id,
[other_fields],
MAX(CASE WHEN attributes.title='Model #' THEN attributes_values.value END) AS Model_N,
MAX(CASE WHEN attributes.title='Part #' THEN attributes_values.value END) AS Part_N
FROM
content INNER JOIN attributes_values
ON content.id = attributes_values.content_id
INNER JOIN attributes
ON attributes_values.attribute_id = attributes.id
GROUP BY
id,
[other_fields]
HAVING
MAX(CASE WHEN attributes.title='Show in Cart' THEN attribute_values.value END)='1'
一点解释:
CASE WHEN attributes.title='Model #' THEN attributes.value END
将返回属性为'Model#'时的值,否则返回NULL,并且
MAX(...)
将返回最大的非NULL值,这是attributes.title ='Model#'的值。
动态属性
MySQL不具有旋转功能,因此,如果你希望你的名单是动态的,你必须dinamically创建的所有属性SQL字符串,并执行该字符串。
您可以获取从attributes
表中的所有属性:
SELECT
CONCAT(
'MAX(CASE WHEN attributes.title=''',
REPLACE(attributes.title, '''', ''''''),
''' THEN attributes_values.value END) AS `',
REPLACE(attributes.title, '`', '``'),
'`'
)
FROM
attributes;
,并与GROUP_CONCAT结合在一起的一切:
SELECT GROUP_CONCAT(...the concat above...)
FROM attributes;
所以你最终的查询将是这样的:
SELECT
CONCAT('SELECT content.id,',
GROUP_CONCAT(
CONCAT(
'MAX(CASE WHEN attributes.title=''',
REPLACE(attributes.title, '''', ''''''),
''' THEN attributes_values.value END) AS `',
REPLACE(attributes.title, '`', '``'),
'`'
)
),
' FROM content INNER JOIN attributes_values ',
'ON content.id = attributes_values.content_id ',
'INNER JOIN attributes ',
'ON attributes_values.attribute_id = attributes.id ',
'GROUP BY id HAVING ',
'MAX(CASE WHEN attributes.title=''Show in Cart'' THEN attributes_values.value END)=''1'''
)
FROM
attributes
INTO @SQL;
现在@SQL变量将保存查询的值t Ø执行,你可以执行:
PREPARE stmt FROM @sql;
EXECUTE stmt;
这里唯一的问题是,GROUP_CONCAT有maximum length limit,可以增加它,如果你的表是持有许多属性。
请参阅示例小提琴here。
嘿,我认为这会奏效!我必须对第一个CASE语句进行一次更改,然后对attributes_values.value END进行更改,否则它看起来不错。只是好奇,是否有办法在CASE动态之后使AS成为可能。例如,类似于MAX(CASE WHEN attributes.title ='Model#'THEN attributes.value END)AS attributes.title? – versalle88 2015-04-10 13:53:26
哇!很好的答案。这太棒了。一个快速的扩展......这将有可能选择所有不同的attributes.titles并获取它们的适当值并将其添加到select语句中?我个人希望避免指定每个字段的标题来抓取 - 通常这样的EAV结构的目的是具有任意数量的字段。 – 2015-04-10 14:05:15
@fthiella这是伟大的东西!非常感谢您的帮助。 – versalle88 2015-04-10 14:56:38