Расширенная фильтрация в интернет-магазине с MySQL
Я делаю интернет-магазин на MySQL и PHP с дизайном базы данных похоже на это (см. картинку)
У меня нет проблем с предметами с несколькими ценами с различными характеристиками, как в вопросе, связанном выше.
Это будет компьютерный интернет-магазин, поэтому мне не нужно составлять спецификации для каждого продукта. Я могу классифицировать их, например. Смартфоны, ноутбуки … и добавить спецификации для каждой категории.
Смартфоны могут иметь: марку, тип процессора, размер экрана, цвет, батарею, цену и т. Д.
Ноутбуки: Процессор, Видеокарта, Марка, Цена, Размер, …
Я также не вижу необходимости в [combinations] Стол, как по моему, было бы много хранить без надобности.
Моя проблема с фильтрацией продуктов. Я могу легко выбрать все из них, или только один, или, например. выберите все телефоны белого цвета или все телефоны белого или золотого цвета, но я не могу выбрать все телефоны белого цвета с 8-мегапиксельной фронтальной камерой.
Табличные структуры:
[products]: id, name, category_id, описание
[categories]: id, имя
[specifications]: id, category_id, name
[spec_values]: id, product_id, spec_id, value
Следующий SQL возвращает все отличные продукты, которые являются золотыми или имеют 8-мегапиксельную фронтальную камеру.
SELECT products.id, products.name, spec_values.value AS Filter
FROM products, categories, specifications, `spec_values`
WHERE products.category_id = categories.id
AND spec_values.product_id = products.id
AND ((specifications.name = 'Color' AND spec_values.value = 'Gold')
OR (specifications.name = 'Front Camera' AND spec_values.value = '8MP'))
GROUP BY products.id;
Я хочу иметь логичный AND вместо OR между ними, но простое изменение ничего не возвращает, так как это не может быть выполнено в пределах одной строки, потому что имена и значения моей спецификации хранятся в нескольких строках.
Одним из решений будет SQL CREATE TEMPORARY TABLE для каждого фильтра, но я думаю, что это потребует много ненужной работы на стороне сервера.
Другим было бы получить все непонятные значения и перебрать их с помощью PHP и проверить, все ли они существуют; если да, то отобразите их на странице, но, тем не менее, этот тоже может быть грязным.
Есть ли лучшее решение, которое я не заметил из-за моей неопытности в SQL?
Спасибо заранее за любую помощь. Хорошего дня!
Решение
Вы можете использовать агрегацию для этого. Включить HAVING пункт, чтобы проверить, что количество соответствует количеству фильтров. Если вы не вставили точное значение спецификации дважды (или более), вы будете знать, что для продуктов, удовлетворяющих этому условию, должны быть отфильтрованы все значения спецификации. Если это не так, одна строка (или несколько строк), то есть одно (или более) значение спецификации, отсутствует.
SELECT products.id,
products.name
FROM products
INNER JOIN spec_values
ON spec_values.product_id = products.id
INNER JOIN specifications
ON specifications.id = spec_values.spec_id
WHERE specifications.name = 'Color' AND spec_values.value = 'Gold'
OR specifications.name = 'Front Camera' AND spec_values.value = '8MP'
GROUP BY products.id,
products.name
HAVING count(*) = 2;
Кстати, желательно использовать явный синтаксис соединения в списке через запятую в FROM пункт для лучшей читаемости.
Другие решения
Других решений пока нет …