Расчет количества товаров (эффективно) в многогранном поиске с помощью PHP, MySQL
Скажем, у меня есть страница, которая отображает все доступные ноутбуки в интернет-магазине. Эти результаты можно уточнить, активировав определенные фильтры, такие как марка, цена, вес, объем памяти, дисковое пространство, например:
Brand
[ ] Dell (16)
[ ] HP (12)
[ ] Lenovo (9)
Memory
( ) ≥ 2 GB (37)
( ) ≥ 4 GB (24)
( ) ≥ 8 GB (8)
Price
( ) ≤ 200 $ (3)
( ) ≤ 300 $ (12)
( ) ≤ 500 $ (22)
В настоящее время я вычисляю количество этих продуктов с помощью одного запроса, результаты которого я перебираю в PHP, чтобы разделить их на эти категории (т. Е. «Память, ≥ 4 ГБ»). Проблема с этим подходом состоит в том, что когда, например, выбрано «≥ 4 ГБ» и показаны результаты, подсчет продуктов становится нелогичным.
MySQL (упрощенно):
SELECT brand, memory, price FROM Laptops WHERE memory >= 4000
PHP (упрощенно):
while($row = msqli_fetch_assoc($results)) {
if($row['memory'] >= 2000) {
$product_count['memory']['2gb']++;
}
}
Результаты:
Memory
( ) ≥ 2 GB (24) <-- should be 37
(o) ≥ 4 GB (24)
( ) ≥ 8 GB (8)
Потому что сейчас я использую запрос с условием WHERE memory >= 4000количество продуктов ограничено текущими 24 результатами (которые соответствуют активированному фильтру), и поэтому «≥ 2 ГБ» теперь также имеет счет 24. Это правда, что из 24 результатов 24 будут иметь 2 ГБ или более памяти, но это не относится к пользователю, который должен знать, сколько результатов будет показано, если будет выбрано 2 ГБ.
Я мог бы запросить базу данных для каждого фильтра в отдельности, опуская WHERE состояние этого фильтра таким образом, чтобы на его счет продукта не влияли его собственные настройки, но все равно влияли любые другие фильтры (например, бренд). Тем не менее, я планирую иметь по крайней мере дюжину фильтров, так что это приведет к еще дюжине запросов, что значительно увеличит нагрузку на довольно простое приложение.
Возможно, существует более эффективный способ подсчета количества товаров, при котором требуется меньше запросов?
Решение
Вам необходимо создавать свои фасетные запросы вне каких-либо фильтров, выбранных пользователем, так как число фасетов никогда не изменится. Вероятно, вы этого не делаете, поэтому, когда пользователь выбирает 4 ГБ, ваш запрос становится:
SELECT brand, memory, price FROM Laptops WHERE memory >= 4000
Когда это должно быть:
SELECT brand, memory, price FROM Laptops WHERE memory >= 2000
Я советую вам переписать свою фазовую логику, чтобы не учитывать ваши фильтры и оптимизировать запросы для подсчета, а не в PHP:
SELECT count(*) as total from Laptops WHERE memory >= 2000;
SELECT count(*) as total from Laptops WHERE memory >= 4000;
Увы, если у вас выбрано несколько фильтров, это станет более сложным, поскольку вы, скорее всего, захотите рассмотреть другие фильтры в запросах, которые НЕ находятся в одном фасетном разделе.
Так что … если огранен брендом, используйте только этот запрос для памяти:
SELECT count(*) as total from Laptops WHERE memory >= 2000 and brand in ('HP', 'Dell');
SELECT count(*) as total from Laptops WHERE memory >= 4000 and brand in ('HP', 'Dell');
Но для аспекта бренда для того же набора результатов не учитывайте память, поскольку она не выбрана:
SELECT count(*) as total from Laptops WHERE brand = 'HP';
SELECT count(*) as total from Laptops WHERE brand = 'Dell';
SELECT count(*) as total from Laptops WHERE brand = 'Lenovo';
Увы, если фильтровать по марке и памяти, учитывайте и цену:
SELECT count(*) as total from Laptops WHERE price < 200 and memory between 2000 and 4000 and brand in ('HP', 'Dell);
Да, сделать все правильно может быть сложно, особенно если учесть, что я не охватил случай, когда вы, вероятно, хотите, чтобы только один вариант был выбран по цене, а несколько — по бренду. Вот почему большинство людей обращаются к http://lucene.apache.org/solr/ для граненого поиска, который просто возвращает набор результатов, ограненный любыми полями, которые вы запрашиваете.
Другие решения
Ошибка в моем мышлении состояла в том, что мне понадобился дополнительный запрос для каждого возможного фильтра, чтобы точно рассчитать количество продуктов. Тем не менее, подсчеты являются неточными только для параметров (радио) в текущем выбранном фильтре (как, например, «Память» выше), поэтому, если я просто пересчитываю счетчики продуктов только для активных фильтров (проверяя текущие параметры $ _GET), это резко уменьшает число необходимых запросов.
Поэтому у меня будет один запрос со всеми включенными фильтрами, например:
$query = 'SELECT id, brand, memory, price, stock FROM Laptops
WHERE stock > 0 AND memory >= 4000 AND price <= 200';
Я зациклюсь на этом с помощью PHP, чтобы разделить результаты на категории фильтров и рассчитать их количество, а затем для каждого активного фильтра я сброс эти подсчеты и пересчитать их на основе запроса, который удалил этот фильтр с помощью регулярного выражения, как preg_replace('(AND memory >= \d+)','',$query);,
$query = 'SELECT id, brand, memory, price, stock FROM Laptops
WHERE stock > 0 AND price <= 200';
Основываясь на этом запросе, подсчеты для памяти теперь будут точно отражать результаты для каждого параметра, но при этом будут учитываться любые другие установленные фильтры (т. Е. Вне области действия фильтра памяти):
Memory
( ) ≥ 2 GB (37)
(o) ≥ 4 GB (24)
( ) ≥ 8 GB (8)
Теперь пользователи знают, что 4 ГБ ограничивают список 24 элементами, а переключение на 2 ГБ даст больше результатов.
Если никакие фильтры не активны, мне не понадобятся дополнительные запросы для расчета количества товаров. Если пользователь применяет один или несколько фильтров, мне потребуется только один дополнительный запрос для каждого из них, и я доволен этим.
Я надеюсь, что проблема может заключаться в том, что запрос позволяет изменить запрос на приведенный ниже и выполнить его. Спасибо
SELECT brand, memory, price FROM Laptops WHERE memory >= 2000