MySQL EAV SELECT один-ко-многим результатам в одной строке

Question

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

То, что я хотел бы, это один запрос (или группа подзапросов), который может вернуть соответствующие данные, чтобы избежать необходимости манипулировать этим в программировании.

Немного больше информации о таблицах: атрибуты имеют три записи (Model #, Part # и Show in Cart).

Мне нужно выбрать все записи из содержимого, где attribute_value для Show in Cart = 1. Наряду с этим, я бы хотел, чтобы другие связанные attribute_value возвращались как их связанные 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), не стесняйтесь включать его. Кроме того, это заранее установленная схема таблиц, которую я не могу изменить, поэтому мне нужно решение запросов для вышеупомянутых таблиц, а не предложение по улучшению дизайна таблицы.

Любая помощь будет оценена.

Ура!

3

entity-attribute-value joomla mysql php

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Вместо того, чтобы объединять таблицу несколько раз для каждого атрибута, вы можете использовать агрегированную функцию, такую как MAX (), в сочетании с оператором CASE WHEN:

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, которое является значением где attribute.title = ‘Model #’.

MySQL не имеет функции Pivot, поэтому, если вы хотите, чтобы ваш список был динамическим, вы должны динамически создать строку 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 будет содержать значение запроса, который будет выполнен, и вы можете выполнить его с помощью:

PREPARE stmt FROM @sql;
EXECUTE stmt;

Единственная проблема здесь заключается в том, что GROUP_CONCAT имеет предел максимальной длины, Вы можете увеличить его, если ваша таблица содержит много атрибутов.

Пожалуйста, посмотрите пример скрипки Вот.

3