Как используя mySQL отфильтровать товары по EAV свойствам, если указано больше одного свойства?

Question

[Горшков Антон]

Делаю фильтр товаров по свойствам, приходит к тому, что пишу дополнительное условие (код атрибута равен X и значение атрибута = Y И код другого атрибута из той же таблицы равен Z и значение атрибута равно I)

(
AND ((`lists_items`.`slug`='biglist') AND (`parameters`.`slug`='list-size'))
AND - !его заменял на OR
((`lists_items`.`slug` IN ('black', 'green')) AND (`parameters`.`slug`='tea-type'))
)

где lists_items - это элементы списка, из которых выбираются значения свойств
parameters - это сущность для параметра товара
products_parameters - это сущность для значений параметра товара, здесь связывается возможное значение атрибута из lists_items и товар из products
products - таблица с товарами

*Если заменяю отмеченный AND на OR - находит Или товары по одному свойству, или по другому. Но с AND не находит НИЧЕГО. Хотя есть товары, которые соответствуют обоим критериям - то есть есть товары, у которых имеются оба свойства и одно из переданных значений.

Весь запрос ниже (biglist - значение свойства list-size, а black и green - значения параметра tea-type).

SELECT * FROM `products` LEFT JOIN `sub_sections` ON `products`.`subsection_id` = `sub_sections`.`id`
 LEFT JOIN `products_parameters` ON `products`.`id` = `products_parameters`.`product_id`
 LEFT JOIN `lists_items` ON `products_parameters`.`list_item_id` = `lists_items`.`id` 
LEFT JOIN `parameters` ON `products_parameters`.`parameter_id` = `parameters`.`id`
 WHERE (`sub_sections`.`slug`='first_sub')
 AND (((`lists_items`.`slug`='biglist') AND (`parameters`.`slug`='list-size')) 
AND ((`lists_items`.`slug` IN ('black', 'green')) AND (`parameters`.`slug`='tea-type')))

Пишу на Yii2, поэтому объяснение сразу в ActiveQuery - тоже очень поможет.

Если AND, котрый я указал заменить на OR - находит верно - то есть или свойство такое-то имеет значение такое-то, или совпадает иное свойство с иным значением. Но мне нужно, чтобы совпадения были по всем свойствам, поэтому пишу AND. И вот тут у меня всегда пустой результат. Отчего это может быть? Какой запрос в итоге нужен? Убил на это весь день.

Comments

[Максим Федоров]

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

[Горшков Антон]

Максим Федоров: Есть свойства товаров, есть разделы, есть сами товары, есть возможные значения свойств товаров.
Товары связаны с разделами по id раздела
Свойства тоже связаны с разделами по id раздела и по этому же полю с товарами (когда общий раздел)
Для связи между возможными значениями свойств товара и товаром есть талица, где указывается id товара, id свойства, id возможного значения.

Передается массив, где ключи - это свойства товаров, а значения - это идентификторы возможных значений этог свойства.

Необходимо вернуть товары, у которых имеется хотя бы одно совпадение значения по КАЖДОМУ свойству. То есть если например у чая в фильтре задано свойство "размер листа" со значением "курпный", а свойство "тип" - зеленый, черный, то должен вернуться обязательно крупнолистовой чай, но или зеленый или черный.

У меня получается ИЛИ зеленый, или черный ИЛИ крупнолистовой. Если заменяю на И крупнолистовой - пустой ответ, хотя судя по даным такого быть не может.

[Горшков Антон]

Александр +: отформатировал :)

Answer 1

[Горшков Антон]

ИТОГ

Решил вот таким запросом

SELECT p.*, po1.slug 'P1', po2.slug 'P2', prop1.slug 'S1', prop2.slug 'S2' 
 
FROM products p       
 
INNER JOIN products_parameters p2o1 ON p.id = p2o1.product_id
INNER JOIN lists_items po1  ON po1.id = p2o1.list_item_id
LEFT JOIN parameters prop1 ON prop1.id = p2o1.parameter_id
 
INNER JOIN products_parameters p2o2 ON p.id = p2o2.product_id
INNER JOIN lists_items po2  ON po2.id = p2o2.list_item_id
LEFT JOIN parameters prop2 ON prop2.id = p2o2.parameter_id
WHERE
(`po1`.`slug` IN ('black', 'green') AND `prop1`.`slug` = 'tea-type') AND (`po2`.`slug`='biglist' AND `prop2`.`slug` = 'list-size')

Не знаю насколько это корявый метод. Теперь буду работать над переносом этого кода в Yii2 видимо от ActiveQuery придется отказаться и формаировать через Query.

Comments

[Сергей]

Как это сработает если параметров не 2 (тип и размер) а например 3 или 4? Дополнительно добавляем:

INNER JOIN products_parameters p2o2 ON p.id = p2o2.product_id
INNER JOIN lists_items po2 ON po2.id = p2o2.list_item_id
?

Answer 2

[Максим Тимофеев]

1. Объяснили Вы весело.
2. Для фильтров проще сделать SearchModel тем более что ее можно сгенерировать через GII, получить в результате готовое решение, к которому останется добавить join
3. Вот что стоит почитать:
www.yiiframework.com/wiki/653/displaying-sorting-a...
https://nix-tips.ru/yii2-sortirovka-i-filtr-gridvi...
www.yiiframework.com/doc-2.0/guide-db-active-recor...

После прочтения у Вас должны появится уже конкретные вопросы, на них с удовольствием отвечу.

Comments

[Горшков Антон]

Ну, простите, понятно, что я использую поисковую модель. К тому же я не так мало понимаю в yii, как Вам могло показаться и проблема глубже и серьёзнее.

Я долго думал как объяснить суть задачи и понял, что ответ вообще знают не многие к моему удивлению (мне казалось, что MySQL для таких задач подходит). Но вот суть проблемы.

Итак, есть EAV. Есть свойство. Одно, два.. три... у свойств есть значения. У каждого несколько. Выбрать какое-либо можно в фильтре. Результат должен быть как, к примеру, на сайте м-видео: если выбрано несколько значений свойства одного параметра - то результирующий товар должен иметь хотя бы одно из выбранных значений для каждого свойства. Например выбран цвет красный и желтый - в выдаче будут только красные или желтые товары. Но если при этом выбрана форма "прямоугольная", то у товаров обязательно должен быть красный или желтый цвет и обязательно прямоугольная форма.

Вопрос не в том в каком месте это пишется, не нарушается ли MVC-паттерн и приятен ли этот код вообще. Вопрос в составлении запроса. И как я понял, на MySQL это как-то делают, но решения выходят очень тяжлыми. Но мне хотя бы одно из них узнать :(

[Максим Тимофеев]

Горшков Антон: Это реализуемо в search моделе. Допустим, выбрал пользователь цвет: red, black и blue и размер xl
в search model прилетело:

['size'=>xl,'color'=>['0'=>'blue', '1'=>'red',  '2'=>'black', ]]

фильтруем:

$query->andFilterWhere(['size'=>$this->size]);
$arr = [];
foreach($this->color as $color){
$arr = array_merge($arr,['or',['color'=>$color]]);
}
// мог ошибиться, но думаю идея понятна, должно получиться ['or',['color'=>'red'],['or',['color'=>'black'],['color'=>'blue']]]

$query->andFilterWhere($arr);

[Горшков Антон]

Максим Тимофеев: я сначала тоже так думал. Но это работает с OR, а мне нужно AND - о чем я и написал. А AND вернет пустую выдачу, потому что у одной записи а таблице значений свойств только одно значение. То есть когда дана строка значение:красный, свойство:цвет, то она отдастся если я скажу красный или желтый. Но если я напишу AND, то он не отдастся, потмоу что в каждой строке значение только одно.
По связям я получаю несколько записей значений для всех атрибутов, но я не могу найти способ, чтобы вернуть только те записи, с которой связана запись с заданным красным цветом И прямоугольной формой. ИЛИ - пожалуйста, но И не выходит. Нужен другой подход. Увы мне и дедлайн мне Т_Т

[Максим Тимофеев]

Горшков Антон: Все должно работать, проверенно много раз. Либо Вы не верно задачу ставите. Я привел пример где выборка идет по или красный или синий или черный. Если у Вас в ячейке цвета через запятую например, тогда нужен like, но я бы так не хранил.

[Максим Тимофеев]

Горшков Антон: Дайте пример баз своей, что как храниться, что бы мы точно говорили о одинаковых вещах

[Горшков Антон]

Максим Тимофеев: я же говорю "ИЛИ работает". Надо И. Есть связь много ко многим между атрибутами и их значениями из списка. И выбрать запись, у которой есть значение красного ИЛИ желтого цвета - легко. Сложно - выбрать ту, с которой связана запись И красного И желтого цвета.

[Максим Тимофеев]

Горшков Антон: тогда просто or сменить на and

[Максим Тимофеев]

Максим Тимофеев: вопрос только к тому как они хранятся (цвета)

[Максим Тимофеев]

Горшков Антон:

$arr = [];
foreach($this->color as $color){
$arr = array_merge($arr,['and',['color'_.$color=>1]]);
}
// мог ошибиться, но думаю идея понятна, должно получиться ['and',['color_red'=>1],['and',['color_black'=>1],['color_blue'=>1]]]

$query->andFilterWhere($arr);

[Горшков Антон]

Максим Тимофеев:
В итоге то же самое и получилось
lists_items - это список возможных значений свойств (из них формируются списки, которыми заполняются свойства)
parameters - это сами свойства
Вот здесь и условие: если код значения свойства biglist и код свойства list-side И если код значения свойства 'black' или 'green' и код свойства tea-type. При замене И на ИЛИ работает корректно. Однако с И всегда возвращает пустую выборку.

WHERE (`sub_sections`.`slug`='first_sub')
 AND (((`lists_items`.`slug`='biglist') AND (`parameters`.`slug`='list-size')) 
AND ((`lists_items`.`slug` IN ('black', 'green')) AND (`parameters`.`slug`='tea-type')))

[Горшков Антон]

Максим Тимофеев:
таблица со значениями свйоств такая (products_parameters):
id - ключ,
parameter_id - айдишник свойства,
product_id - айдишник товара,
list_item_id - айдишник значения из списка. (там в поле slug и есть эти green, yellow, red и так далее).

И я думаю, что AND не работает потому, что связь-то много ко многим, но у каждой записи из этой таблицы всегда есть только один параметр и только один айдишник. Поэтому ИЛИ проходит, так как у записи с красным цветом при фильтре "красный или желтый" совпадение будет. А вот "красный И желтый", при том, что там только красный...

И это несмотря на то, что у свойства есть несколько элементов products_parameters, и у них сумарно есть все искомые значения свойств

[Горшков Антон]

Максим Тимофеев: Так вот, и выходит следующее:
допустим я говорю, что цвет должен быть или красный или желтый - это срабатывает, потому что есть свойство color, у которого есть или одно или оба значения. Но если у того же товара должно быть ещё и свойство shape с некоторым значением и притом должн быть обязательно - происходит отказ.

[Горшков Антон]

Мой мозг выдал пока только одну концепцию - использовать вложенные запросы для того, чтобы каждый раз выбирать значения по одному свойству из предыдущего запроса. Сначала выбрать желтые, либо зеленые товары, а потом ИЗ НИХ выбрать прямоугольные. Другое дело, что не очень получается составить такой запрос даже просто на бумаге, а через ActiveQuery возможно нельзя вовсе (а хотелось бы именно через ActiveQuery). Но это хотя бы концепция, которую мой разум породил :)
Хоть в исходнии битрикса лезь... там же есть такое.

[Максим Тимофеев]

Горшков Антон: list_item_id лишнее зачем? Видимо дело в неправильной структуре у Вас. Должна быть таблица типа:
id
value (тут значение)
products_parameters_id (тут связь с параметром)
products_id (тут связь с продуктом)

[Горшков Антон]

Продолжаю свой блог XD
Нашел интересный способ - "развернуть" таблицу в плоскую.
buysql.com/mysql/14-how-to-automate-pivot-tables.html
Тогда у одной записи будут доступны сразу все занчения. Но это нужен фоновый процесс, который будет обновлять такую вспомогательную таблицу

[Максим Тимофеев]

Горшков Антон: Вы что-то курите? Какие фоновые процессы. Элементарная тривиальная задача при правильной структуре.

[Максим Тимофеев]

Горшков Антон: Все я спать. Не могу больше выносить это.

[Горшков Антон]

Максим Тимофеев: >list_item_id лишнее зачем?
Значения выбираются из списков. Поэтому вместо value там id выбранного значения. Что в этом неправильного?
>Вы что-то курите?
Нет. Погуглите проблему фасетного EAV поиска. Все, кто с ней сталкивался, что-то курят.

[Максим Тимофеев]

Горшков Антон: Каждое решение оправдано в том или ином случае, в Вашем - это глупость.

[Горшков Антон]

Максим Тимофеев: я ещё ничего и не решил. Самый правильный с точки зрения mySQL способ я нашел в этой статье www.skillz.ru/dev/php/article-Obyasnenie_SQL_obedi... , но
1) Не очень пока понимаю как такое сконструировать в ActiveQuery
2) Боюсь тормозов

[Горшков Антон]

Максим Тимофеев: это в описании "Множественное объединение multi join". Там же даже цитата "Просто перечислить условия вариантов в подзапросе/джоине через OR/AND не сработает, ". Так что я серьёзно. Это вообще мой первый заход на Тостер, потому что всегда упорно гуглю и ищу решения, но здесь действительно всё оказалось очень заковыристо.