EAV фильтры: фронтенд и бэкенд. Проблема 1 значения и как реализовать правильный подсчет значений и disable несуществующих комбинаций?

Question

[Ginza9]

Здравствуйте. Входные данные:
1. Интернет-магазин
2. EAV-модель хранения атрибутов ( таблицы: attribute (aid, name), atrribute_value (vid, aid, value), product_attribute (pid, aid, vid ) )
3. В БД нет такой сущности, как фильтр, атрибуты не привязаны к категориям и т.д.
4. Список фильтров строится по товарам категории ( или же по товарам из поиска )

Проблема в следующем:
не получается сделать адекватную работу фильтров, как оно должно быть. Ранее при выборе значения атрибута в секции этого атрибута оставалось только выбранное значение (что и логично, ведь там INNER JOIN). Теперь просто отдаются всегда все фильтры категории, но проблема в том, что счетчики значений не пересчитываются =(
Перепробовал уже много чего: в том числе и всякие костыли на фронтенде, никак не могу найти верное решение.

Запрос построения списка фильтров для категории:

SELECT DISTINCT pa.attribute_id, a.name, a.title, av.value, av.value_id, COUNT(*) as filter_counter FROM product_attribute pa INNER JOIN attribute_value av ON av.value_id = pa.value_id INNER JOIN attribute a ON a.attribute_id = pa.attribute_id AND a.is_displayed = '1' WHERE pa.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '6') GROUP BY av.value_id ORDER BY a.sort_order ASC

Запрос выборки товаров по фильтрам:

SELECT p.*,pd.* FROM product p LEFT JOIN product_description pd ON pd.product_id = p.product_id LEFT JOIN product_to_category p2c ON p2c.product_id = p.product_id INNER JOIN product_attribute pa0 ON pa0.product_id = p.product_id AND pa0.attribute_id = 27 INNER JOIN product_attribute pa1 ON pa1.product_id = p.product_id AND pa1.attribute_id = 45 WHERE p2c.category_id = '6' AND ((pa0.value_id = '496') AND (pa1.value_id = '1124')) AND pd.name LIKE '%%' AND p.status = '1' GROUP BY p.product_id ORDER BY p.quantity < 1,p.popularity DESC LIMIT 0,60

Буду признателен любой помощи =) Спасибо

Answer 1

[Lapita12]

Для решения проблемы с подсчетом значений и отключением несуществующих комбинаций фильтров, можно использовать следующий подход:
1. Для каждого атрибута, полученного из запроса построения списка фильтров, нужно выполнить отдельный запрос для подсчета количества товаров, у которых есть значение этого атрибута. Например:

SELECT COUNT(DISTINCT pa.product_id) as filter_counter FROM product_attribute pa WHERE pa.attribute_id = '27' AND pa.value_id IN (SELECT av.value_id FROM attribute_value av WHERE av.attribute_id = '27' AND av.value IN ('Значение 1', 'Значение 2'))

2. Если значение фильтра не выбрано, то нужно использовать общее количество товаров в категории для подсчета значения счетчика. Например:

SELECT COUNT(*) as total_count FROM product p INNER JOIN product_to_category p2c ON p2c.product_id = p.product_id WHERE p2c.category_id = '6'

3. При выборе значения фильтра нужно пересчитывать значения счетчиков для всех остальных фильтров, чтобы отключить несуществующие комбинации. Например:
- При выборе значения атрибута 27 (Значение 1) нужно выполнить запросы для подсчета количества товаров для всех остальных атрибутов:

SELECT COUNT(DISTINCT pa.product_id) as filter_counter FROM product_attribute pa WHERE pa.attribute_id = '45' AND pa.value_id IN (SELECT av.value_id FROM attribute_value av WHERE av.attribute_id = '45' AND av.value IN ('Значение А', 'Значение Б'))

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

SELECT av.value, av.value_id, COUNT(DISTINCT pa.product_id) as filter_counter FROM product_attribute pa INNER JOIN attribute_value av ON av.value_id = pa.value_id WHERE pa.attribute_id = '27' AND pa.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '6') AND pa.value_id IN (SELECT av.value_id FROM attribute_value av WHERE av.attribute_id = '27' AND av.value IN ('Значение 1', 'Значение 2')) AND (SELECT COUNT(DISTINCT pa1.product_id) FROM product_attribute pa1 WHERE pa1.attribute_id = '45' AND pa1.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '6') AND pa1.value_id IN (SELECT av1.value_id FROM attribute_value av1 WHERE av1.attribute_id = '45' AND av1.value IN ('Значение А', 'Значение Б'))) > 0 GROUP BY av.value_id

4. Для отображения выбранных фильтров и их значений на странице нужно использовать параметры GET-запроса или cookies, чтобы сохранить выбранные значения между запросами. Например:
- При выборе значения атрибута 27 (Значение 1) нужно добавить параметр в GET-запрос или сохранить значение в cookies:
http://example.com/category/?attr27=Значение+1
или

setcookie('attr27', 'Значение 1');
- При выполнении запроса выборки товаров нужно проверять наличие параметров GET-запроса или cookies и добавлять соответствующие условия в запрос. Например:

SELECT p.*,pd.* FROM product p LEFT JOIN product_description pd ON pd.product_id = p.product_id LEFT JOIN product_to_category p2c ON p2c.product_id = p.product_id INNER JOIN product_attribute pa0 ON pa0.product_id = p.product_id AND pa0.attribute_id = 27 INNER JOIN product_attribute pa1 ON pa1.product_id = p.product_id AND pa1.attribute_id = 45 WHERE p2c.category_id = '6' AND ((pa0.value_id = '496') AND (pa1.value_id = '1124')) AND pd.name LIKE '%%' AND p.status = '1' AND (pa0.value_id IN (SELECT av.value_id FROM attribute_value av WHERE av.attribute_id = '27' AND av.value IN ('Значение 1'))) GROUP BY p.product_id ORDER BY p.quantity < 1,p.popularity DESC LIMIT 0,6.