Форум MySQL

Есть 3 таблицы
tovar - в которой хранится список товаров, с какими-то характеристиками которые есть у любого товара(название, описание, цена)
params - список всех возможны параметров у различных товаров(цвет, вес, материал, и тд)
tovar_params - таблица соответствия значения параметра у товара(товар 1 - майка, цвет - белый, вес - 500гр, материал - мешковина)

CREATE TABLE `tovar` (   `tovar_id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `tovar_name` varchar(300) NOT NULL,   `tovar_desc` varchar(500) NOT NULL,   `tovar_price` float(10,2) NOT NULL,   `catalog_id` int(11) unsigned NOT NULL,   PRIMARY KEY (`tovar_id`),   KEY `catalog_id` (`catalog_id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251; CREATE TABLE `params` (   `param_id` int(20) unsigned NOT NULL AUTO_INCREMENT,   `param_group_id` int(11) unsigned NOT NULL,   `param_name` varchar(200) NOT NULL,   `param_type` varchar(7) NOT NULL,   PRIMARY KEY (`param_id`),   KEY `param_group_id` (`param_group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251; CREATE TABLE `tovar_params` (   `tovar_param_id` int(20) unsigned NOT NULL AUTO_INCREMENT,   `param_id` int(20) unsigned NOT NULL,   `tovar_id` int(11) unsigned NOT NULL,   `param_value` varchar(200) NOT NULL,   PRIMARY KEY (`tovar_param_id`),   KEY `param_id` (`param_id`),   KEY `tovar_id` (`tovar_id`),   KEY `param_value` (`param_value`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

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

SELECT   COUNT(tovar_id) AS cnt  FROM tovar  WHERE    tovar_id IN(SELECT tovar_id FROM  tovar_params WHERE param_id = 2 AND param_value = 'белый')   AND tovar_id IN(SELECT tovar_id FROM  tovar_params WHERE param_id = 3 AND param_value = 'мешковина')

Возможно структура таблиц не идеальна для таких выборок? Буду рад услышать ваши предложения по ее изменению, заранее спасибо

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

SELECT COUNT( * ) cnt FROM tovar t LEFT JOIN tovar_params tp ON tp.tovar_id = t.tovar_id LEFT JOIN params pr ON pr.param_id = tp.param_id WHERE tp.param_value = 'белый' AND tp.param_value = 'мешковина'

Если я, конечно, правильно поняла, что вам нужно.

param_id = 2  param_id = 3

В вашем запросе нет условия по идентификатору параметра, поэтому количество будет не верным при наличии одинаковых param_value у разных param_id, что в полне реально

>В вашем запросе нет условия по идентификатору параметра,

Уже есть :)

SELECT COUNT( * ) cnt FROM tovar t LEFT JOIN tovar_params tp ON tp.tovar_id = t.tovar_id LEFT JOIN params pr ON pr.param_id = tp.param_id WHERE tp.param_value = 'белый' AND tp.param_value = 'мешковина' AND pr.param_id = 3

Смотрите
param_id 2 это цвет
param_id 3 это материал
В вашем последнем запросе это не учтено, но если и добавить

WHERE (tp.param_value = 'белый' AND pr.param_id = 2) AND (tp.param_value = 'мешковина' AND pr.param_id = 3)

Не найдет ничего

Там ведь в этом вся и проблема, что информацию по конкретному товару мы можем получить в виде строк

tovar_id param_id value 1 2 белый 1 3 мешковина 2 2 черный 2 3 хлопок

Если бы она была в виде колонок, было бы совсем просто.

tovar_id param_id_1_value param_id_2_value param_id_3_value .... 1 null белый мешковина 2 null черный хлопок

>Смотрите
>param_id 2 это цвет
>param_id 3 это материал
>В вашем последнем запросе это не учтено, но если и добавить
>

WHERE (tp.param_value = 'белый' AND pr.param_id = 2) >AND (tp.param_value = 'мешковина' AND pr.param_id = 3)

Ну я вам разве так говорила...

SELECT COUNT( * ) cnt FROM tovar t LEFT JOIN tovar_params tp ON tp.tovar_id = t.tovar_id LEFT JOIN params pr ON pr.param_id = tp.param_id WHERE tp.param_value = 'белый' AND tp.param_value = 'мешковина' AND pr.param_id IN(3,4,5,6)

WHERE tp.param_value = 'белый'  AND tp.param_value = 'мешковина'

tp.param_value должен разорваться и принимать одновременно (AND) два разных значения?))

Вот видите, когда вас и Trianon`a нет, ошибки некому поправить :)))

SELECT COUNT( * ) cnt FROM tovar t LEFT JOIN tovar_params tp ON tp.tovar_id = t.tovar_id LEFT JOIN params pr ON pr.param_id = tp.param_id WHERE (tp.param_value = 'белый' OR tp.param_value = 'мешковина' ) AND pr.param_id IN(3,4,5,6)

> Если бы она была в виде колонок, было бы совсем просто.

SELECT     `tovar`.*,     `param1_val`.`param_value` AS `цвет`,     `param2_val`.`param_value` AS `материал` FROM     `tovar`     INNER JOIN `tovar_params` AS `param1_val` ON `tovar`.`tovar_id`=`param1`.`tovar_id`     INNER JOIN `params` as `param1` ON `param1_val`.`param_id`=`param1`.`param_id`     INNER JOIN `tovar_params` AS `param2_val` ON `tovar`.`tovar_id`=`param2_val`.`tovar_id`     INNER JOIN `params` as `param2` ON `param2_val`.`param_id`=`param2`.`param_id` WHERE     `param1`.`param_name`="цвет"     AND     `param2`.`param_name`="материал"

вот тут куски условий, живущие в WHERE ну просто в голос кричат "Переселите нас в соотв.join-ON!"

Соглашусь.
Но так будет удобней условие выборки формировать в одном месте:

(`param1`.`param_name`, `param1_val`.`param_value`)=("цвет", "чёрный") AND  (`param2`.`param_name`, `param2_val`.`param_value`)=("материал", "мешковина")

Если бы было:

INNER JOIN `tovar_params` AS `param_color_val` ON `tovar`.`tovar_id`=`param_color_val`.`tovar_id`  INNER JOIN `params` as `param_color` ON `param_color_val`.`param_id`=`param_color`.`param_id`

согласился бы без возражений.

Спасибо большое, все оказалось и правда просто.
Признаюсь честно даже не знал что можно использовать такую конструкцию

(`param1`.`param_name`, `param1_val`.`param_value`)=("цвет", "белый")

PS В вашем коде есть небольшая опечатка, но кому этот запрос пригодится думаю разберутся в чем проблема;)

Может вы еще подскажите как решить проблему если у товара больше 30 параметров?
Иначе рискуем увидеть такое сообщение

mysql can only use 61 tables in a join

Кстати запрос на реальной базе в сотни тысяч товаров, и миллионы параметров выполняется довольно быстро)

Если запрос формируется на основании поисковой формы, то самое простое - ограничить пользователя в количестве задаваемых условий поиска (всё равно десяток-другой фиксированных параметров скорей всего обрежет множество до 0).

Количество JOIN'ов в данном запросе можно сократить в 2 раза, если заранее выбрать все используемые идентификаторы параметров из таблицы `params` и подставлять их в запрос "вручную".

Если 60 параметров мало, можно выполнять запросы по 60 JOIN'ов, а потом пересекать полученные товары по полю `tovar`.`tovar_id`.

SELECT * FROM     `tovar` WHERE     `tovar_id` IN (SELECT `tovar_id` FROM ... запрос с первыми 60 параметрами)     AND     `tovar_id` IN (SELECT `tovar_id` FROM ... запрос со вторыми 60 параметрами)

Можно отказаться от гибкости и пустить таблицу `tovar` на денормализацию расти в ширину - для каждого аттрибута создать отдельное поле. Если у какого то товара не будет конкретного аттрибута, то в соответствующем поле будет NULL. Но врятли такую кучу можно будет заставить быстро работать.

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

Цвет  белый 160  черный 100  синий 20  фиолетовый 20 Материал  хлопок 200  мешковина 1  шелк 40  синтетика 59

Собственно такой вариант решается простым IF

SUM(IF(`param_id` = 2 AND `value` = "белый", 1, 0)) as "2.белый", SUM(IF(`param_id` = 2 AND `value` = "черный ", 1, 0)) as "2.черный ", SUM(IF(`param_id` = 2 AND `value` = "синий ", 1, 0)) as "2.синий ",

Но, еще к этому нужно вывести вот такой вариант

Качество майки  Хорошее(цвет белый,черный,синий + материал хлопок, шелк) 240  Среднее(цвет любой, материал синтетика) 59  Плохое(цвет любой, материал мешковина ) 1

Это как раз таки составной фильтр, и его нельзя сделать в одном запросе с IF. Вот если все параметры в виде колонок, то легко.

Надеюсь я не намудрил с объяснением