Как сделать этот eav-запрос, чтобы получить горизонтальный результат

Дело:

столы:

product:
product_id|name        |
------------------------
1         |iphone 4    |
2         |gallaxy 2   |
3         |blackbery 6 |

product_attribute:

id|product_id|attribute_id
--------------------------------------------------
 1 |1        |2
 2 |1        |6
 .    .        .

attribute:
------------------------------
attribute_id|name  |value|
        1   |width |300
        2   |width |320
        3   |width |310
        4   |height|390
        5   |height|370
        6   |height|380

должен получить результат:

product_id|height|width
 1        |380   |320
 ......................

Редактировать: атрибуты height и width являются его единственной частью атрибутов продукта - продукт должен иметь динамическую возможность добавления пользователем в бэкэнд, как это было сделано в magento, потому что я выбираю eav db design. Пожалуйста, пишите запросы, если это возможно, в случае, если мы не знаем, какие названия продукта.

Спасибо

3 ответа

Есть несколько способов реализовать это. Примерно так должно сработать, присоединяясь к таблице несколько раз для каждого значения атрибута:

SELECT p.product_id,
    a.value height,
    a2.value width
FROM Product p
    JOIN Product_Attribute pa ON p.product_id = pa.product_id 
    JOIN Attribute a ON pa.attribute_id = a.attribute_id AND a.name = 'height'
    JOIN Product_Attribute pa2 ON p.product_id = pa2.product_id 
    JOIN Attribute a2 ON pa2.attribute_id = a2.attribute_id AND a2.name = 'width'

А вот и скрипка.

Вот альтернативный подход с использованием MAX и GROUP BY, который я лично предпочитаю:

SELECT p.product_id,
    MAX(Case WHEN a.name = 'height' THEN a.value END) height,
    MAX(Case WHEN a.name = 'width' THEN a.value END) width
FROM Product p
    JOIN Product_Attribute pa ON p.product_id = pa.product_id 
    JOIN Attribute a ON pa.attribute_id = a.attribute_id 
GROUP BY p.product_id

Удачи.

One approach is to use correlated subqueries in the SELECT list, although this can be less than optimum for performance on large sets. For retrieving just a few rows rows from the product table, it won't be bad. (You'll definitely want appropriate indexes.)

SELECT p.product_id
     , ( SELECT a1.value
           FROM attribute a1
           JOIN product_attribute q1
             ON q1.attribute_id = a1.attribute_id
          WHERE q1.product_id = p.product_id
            AND a1.attribute_name = 'height'
          ORDER BY a1.id
          LIMIT 0,1
       ) AS height_1
     , ( SELECT a2.value
           FROM attribute a2
           JOIN product_attribute q2
             ON q2.attribute_id = a2.attribute_id
          WHERE q2.product_id = p.product_id
            AND a2.attribute_name = 'width'
          ORDER BY a2.id
          LIMIT 0,1
       ) AS width_1
  FROM product p
 WHERE p.product_id = 1

This query will return the row from product, along with the values of the attributes, if they exist. If the attribute values are not found, the query will return a NULL in place of the attribute value. (This differs from the behavior of a query that uses INNER JOIN in place of correlated subquery... where a "missing" row from the attribute or product_attribute table would filter out the row from product being returned.)

The purpose of the LIMIT clauses is to guarantee that the subqueries will return not return more than one row. (If a subquery in the SELECT list were return more than one row, MySQL would return an error.) The purpose of the ORDER BY is to make the query deterministic, again, in the event there is more than one row that satisfies the subquery. (Absent the ORDER BY clause, when there is more than one row, MySQL is free to arbitrarily return whichever row it chooses to.)

For "multi-valued" attributes, the same approach works. We just add more subqueries, but specify LIMIT 1,1 to return the second attribute value, LIMIT 2,1 to return the third value, etc.


(Oh, the joy of an EAV model implemented in a relational database.)


Следовать за:

Q: "... more general case as it happen in eav db that we dont know before which attributes names we have."

A: The relational model is based on the principle that a tuple contains a specified number of columns, of a specified type.

What you are (apparently) trying to do is return a variable number of columns when you run a query. A SELECT statement includes a specific list of expressions to be returned; this cannot vary and the datatypes of the values returned by each expression does not vary from row to row.

The query above returns one instance of a "height" attribute value, and one instance of a "width" attribute value, for each product.

For a more "more general case", we would really expect that each attribute value would be returned on its own separate row.

Более общий запрос, если вы не знаете "заранее", какие атрибуты связаны с продуктом:

SELECT p.product_id
     , a.attribute_id
     , a.name         AS attribute_name
     , a.value        AS attribute_value
  FROM product p
  LEFT
  JOIN product_attribute q
    ON q.product_id = p.product_id
  LEFT
  JOIN attribute a
    ON a.attribute_id = q.attribute_id
 WHERE p.product_id = 1
 ORDER
    BY p.product_id
     , a.name
     , a.attribute_id

Это вернет набор результатов, который можно легко обработать:

product_id attribute_id attribute_name attribute_value
---------- ------------ -------------- ---------------
         1            6 height         380
         1            2 width          320

Q: "похоже, что это должно быть сделано в 2 этапа: 1. получить все имена атрибутов для продукта 2. затем код с помощью серверного кода имен атрибутов в цикле for"

A: Нет, похоже, что один запрос вернет все пары имени и значения атрибута для продукта. Каждое имя / значение атрибута будет находиться в отдельной строке.

Нет необходимости использовать цикл for для генерации дополнительных запросов к базе данных. Да, это возможно, но совершенно не нужно.

Если у вас есть какое-то странное требование составить другой запрос для выполнения к базе данных, чтобы вернуть набор результатов в указанном вами формате, независимо от того, какую обработку вы будете выполнять для обработки набора результатов из оператора "более общего случая", вероятно, будет более эффективным просто обработайте набор результатов, не выполняя больше запросов к базе данных.

Если вам нужно вернуть набор результатов, который выглядит следующим образом:

 product_id height width
 ---------- ------ -----
          1 380    320

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

SELECT 1 AS product_id, '380' AS height, '320' AS width

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

Позвольте мне сначала сказать, что это действительно плохой дизайн. В соответствии с вашим текущим подходом вам потребуется запустить несколько подзапросов или объединений с псевдонимами таблиц, чтобы достичь желаемого результата.

SELECT 
    product_id,
    (
        SELECT product_attribute.value 
        FROM product_attribute, attribute 
        WHERE product_attribute.product_id=product.product_id 
        AND product_attribute.attribute_id=attribute.attribute_id
        AND product_attribute.name = 'width'
    ) AS 'width',
    (
        SELECT product_attribute.value 
        FROM product_attribute, attribute 
        WHERE product_attribute.product_id=product.product_id 
        AND product_attribute.attribute_id=attribute.attribute_id
        AND product_attribute.name = 'height'
    ) AS 'height'
FROM
    product
ORDER BY 
    ...      

Позвольте мне предложить:

attribute
   attribute_sid  (eg, string id)

product
   product_id
   name
   ...

product_attribute
   product_id   (foreign key to product table)
   attribute_sid  (foreign key to attribute table)
   value   

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

SELECT attribute_sid, value FROM product_attribute WHERE product_id = 1

... извлечет все атрибуты и значения, которые могут быть удобно помещены в dict, array, или же map,

Другие вопросы по тегам