Как сконструировать правильный запрос для выборки всех атрибутов товара?

Question

[muhasa]

Есть таблица товаров - products, есть атрибуты attributes и есть связующая таблица atribute_product
Последняя устроена так:
product_id | attribute_id | value
Теперь стоит задача, нужно сделать фильтр товаров по атрибутам.
Например, есть атрибут "тип изделия", там 6 вариантов - классическое, спортивное и т.д.
Есть другой атрибут - "форма изделия" - круглая, овальная, квадратная и т.д.

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

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

SELECT product_id FROM atribute_product WHERE attribute_id = 1 AND (value LIKE "Классическое"  OR value LIKE "Спортивное")

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

Но как сделать запрос, где надо учесть еще и то, что другой атрибут товара должен быть из какого-то множества? У нас ведь 1 запись содержит 1 атрибут и 1 его значение.

Такое выражение...

SELECT product_id FROM atribute_product WHERE (attribute_id = 1 AND (value LIKE "Классическое"  OR value LIKE "Спортивное")) AND (attribute_id = 2 AND (value LIKE "Круглое"  OR value LIKE "Овальное"))

...по мне лишено смысла...

Вот если бы это было не логическое И, а ИЛИ - там, конечно, проще. А конкретно в моем случае надо, чтобы было И.
Вообще, если мысль решить задачу тупо средствами php - просто сформировать несколько множеств и вычленить их пересечение. Но ведь явно же на SQL есть нормальное решение это задачи?

Думал о UNION запросах, но там именно объединение, вот если бы была бы такая же функция, которая именно пересечение делает...

В-общем, уважаемые специалисты, прошу растолковать, как такое решается.

Comments

[muhasa]

aaa000, я понимаю, Вы намекаете на ещё одну таблицу. Но есть такие значения атрибутов, которые вписываются от руки и не имеют повторений и таких около 1800 только сейчас в бд. Смысл в отдельной таблице?

И еще - как бы вообще дополнительная таблица помогла?

Answer 1

[Gennady S]

Тут надо понимать, что Вы пытаетесь выбрать 1 строку с product_id с выборкой по двум строкам. В одной строке, разумеется, не будет значений "тип изделия" и "форма изделия". Следовательно, в одном запросе и с выборкой по одной строке это не решается, по крайней мере "в лоб".

свести выборку по нескольким строкам к одной можно несколькими способами. Навскидку,

SELECT product_id FROM atribute_product WHERE
product_id IN (SELECT product_id FROM product_attribute WHERE attribute_id = 1 AND value IN ("Классическое", "Спортивное") AND
product_id IN (SELECT product_id FROM product_attribute WHERE attribute_id = 2 AND (value = ... OR value = ...))

Или, используя несколько INNER JOIN. Наверняка есть различные оптимизации этих подходов. Например, суб-запрос можно попробовать свести к одному, где, полагаю, получится выбрать количество строк COUNT(product_id) AS cnt и собственно product_id и проверить HAVING cnt = 3 (что может быть осложнено комбинациями и/или).

PS Возможно получится схитрить, избежать вложенные запросы с оператором CASE, группировкой по product_id и опять-таки с проверкой на количество условий:

SELECT product_id, COUNT(product_id) as cnt
WHERE ... CASE WHEN attribute_id = 1 THEN value = "Классическое" WHEN attribute_id = 2 THEN value LIKE "Кругл%" ELSE 0 END ...
GROUP BY product_id
HAVING cnt = 2

Увы, нет возможности попробовать-поизучать разные варианты для ответа на вопрос.

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

Comments

[muhasa]

Про Having и cnt сам уже минут 20 мусолю в голове, а вот Ваш первый вариант очень даже интересный! Фактически это и есть пересечение двух запросов, о котором я писал, но не докумекал! Попробую вечером, отпишусь!

[Gennady S]

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

[muhasa]

Gennady S, первый - это который? Который с Having или который с множественным IN?

[Gennady S]

muhasa, первый, который приведен в коде с под-запросами на IN. На каждый атрибут будет вложенный запрос.

[muhasa]

Gennady S, потестирую

[muhasa]

Gennady S, Да, Вы правы, второй вариант запроса шустрее работает! Благодарю за наводку!

[Gennady S]

muhasa, отметьте ответ — ответом, если решено. Это может быть полезно тем, кто будет искать.

Answer 2

[Антон Р.]

есть атрибут "тип изделия", там 6 вариантов - классическое, спортивное и т.д. - а как у тебя хранятся эти опции?
У тебя получается таблица atribute_product является связующей в отношении "многие ко многим" и заодно содержит имена опций?
Странно как-то.

Comments

[muhasa]

Ну может и странно, Вы подскажите, как бы Вы сделали. Я свой вариант не защищаю, как эффективнее, так и буду делать.
А вообще, у меня да, там многие ко многим. У одного товара много атрибутов, у одного атрибута много товаров. Как бы Вы сделали?..

У тебя получается таблица atribute_product является связующей в отношении "многие ко многим" и заодно содержит имена опций?

Ну да, pivot-значение value

[Gennady S]

muhasa, значения можно хранить в отдельной таблице. Вообще, это известный шаблон проектирования EAV Я в своем текущем проекте храню значения атрибутов в JSON поле. Имея таблицы: продукты, группы продуктов (например, салат с ветчиной или с курицей имеют разную цену), атрибутов, связь атрибутов и групп продуктов, заранее заданные значения без повторений. Связь же таблицы значений и продуктов привязана к продуктам: {attribute_id: value(s)}, где значение может быть идентификатором из таблицы значений, списком идентификаторов или пользовательскими данными — зависимо от типа атрибута.

[muhasa]

Gennady S, хм...интересно, и вроде стройно
Могли бы Вы выложить вариант вашей базы, в деталях хочу посмотреть.

[Gennady S]

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