Несколько MySQL WHERE IN в одном столбце с оператором AND

Нужна помощь с MySQL.

Я создаю интернет-магазин, и у меня возникают проблемы с фильтрацией результатов поиска с несколькими предложениями WHERE IN в одном столбце.

У меня есть таблицы shopitem, filter, filter_option и shopitem_option.

shopitem (товары с названием, описанием, ценой и т. д.)

id
title
price

фильтр (основные категории фильтров, такие как цвет, бренд, пол, размер и т. д.)

id
name

filter_option (параметры фильтра, такие как размеры (S, M, L), цвета (черный, белый) и т. д.)

id
name
filter_id (filter.id)

shopitem_option (связь между товарами и опциями)

id
shopitem_id (shopitem.id)
filter_id (filter.id)
filter_option_id (filter_option.id)

У меня есть такие вещи, как различные кроссовки Nike Air разного размера, цвета, пола и т. д.

Итак, в таблице shopitem_option у меня есть следующее:

id  |  shopitem_id  |  filter_id  |  filter_option_id
-----------------------------------------------------
1   |  Nike Air     |  Color      |  black
2   |  Nike Air     |  Color      |  white
3   |  Nike Air     |  Size       |  40
4   |  Nike Air     |  Size       |  41
5   |  Nike Air     |  Size       |  42
6   |  Nike Air     |  Gender     |  man

Когда я создаю запрос MySQL, он должен быть примерно таким (в чистом SQL-запросе вместо «черный», «мужской» и т. д. есть идентификаторы поля filter_option.id)

SELECT shopitem.*
FROM shopitem
LEFT JOIN shopitem_option.shopitem_id = shopitem.id
WHERE
    shopitem_option.filter_option_id IN (black) AND 
    shopitem_option.filter_option_id IN (41,42) AND 
    shopitem_option.filter_option_id IN (man)

Но это не работает. Если я ищу только 40-й размер или только черный цвет, будут возвращены все туфли 40-го размера или все туфли черного цвета. Но если я комбинирую фильтр для поиска элементов с черным цветом и размером 40, он возвращает пустые результаты запроса.

Таким образом, в основном пользователь может искать обувь ГДЕ цвета (черный ИЛИ белый) И размер (41 ИЛИ 42) И пол (мужской). Как я могу это сделать?


mysql where-in
person fsasvari    schedule 17.11.2015    source источник
comment
Что вы подразумеваете под "это не работает"?   -  person bfontaine    schedule 17.11.2015
comment
@bfontaine Если я буду искать только 40-й размер или только черный цвет, он вернет все туфли 40-го размера или все туфли черного цвета. Но если я комбинирую фильтр для поиска элементов с черным цветом и размером 40, он возвращает пустые результаты запроса...   -  person fsasvari    schedule 17.11.2015
comment
Спасибо, не могли бы вы отредактировать свой вопрос, чтобы добавить это? Это поможет людям, отвечающим вам.   -  person bfontaine    schedule 17.11.2015

Ответы (2)


arrow_upward
1
arrow_downward

Используйте это так

SELECT si.*, GROUP_CONCAT(so.filter_option_id) AS filter_options
FROM shopitem si
LEFT JOIN shopitem_option so ON(so.shopitem_id = si.id)
HAVING
FIND_IN_SET('black', filter_options)
AND  FIND_IN_SET('41', filter_options)
AND FIND_IN_SET('man', filter_options)
GROUP BY si.id

Для подсчета количества si.id из результата вы можете использовать это с подзапросом

SELECT COUNT(res.id) FROM
  (SELECT si.*, GROUP_CONCAT(so.filter_option_id) AS filter_options
   FROM shopitem si
   LEFT JOIN shopitem_option so ON(so.shopitem_id = si.id)
   HAVING
   FIND_IN_SET('black', filter_options)
   AND  FIND_IN_SET('41', filter_options)
   AND FIND_IN_SET('man', filter_options)
   GROUP BY si.id) 
 AS res
person Arun Krish    schedule 17.11.2015
comment
Он говорит, что неизвестный столбец «filter_options» в «где пункт» - person fsasvari; 17.11.2015
comment
Проверь это сейчас. Я изменил WHERE на HAVING. Это будет работать сейчас - person Arun Krish; 17.11.2015
comment
Это работает, спасибо! Как я могу использовать это в подсчете предметов? Если я поставлю COUNT(si.id) вместо si.*, он вернет мне неправильное количество элементов. - person fsasvari; 17.11.2015
comment
вы считаете уникальный идентификатор из результата вывода .. правильно? - person Arun Krish; 17.11.2015
comment
Нет, сначала я запускаю COUNT, чтобы подсчитать все элементы, а затем показываю только 20 с нумерацией страниц... Спасибо за запрос COUNT! - person fsasvari; 17.11.2015
comment
Как запустить group by после having без ошибок? - person vee; 10.05.2021

arrow_upward
3
arrow_downward

IN ... AND IN ... здесь не работает, потому что вы указываете базе данных выбрать shopitem_option, где filter_option_id должен быть black и 41 для одной и той же записи. Используйте EXISTS следующим образом:

SELECT *
  FROM shopitem i
 WHERE EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (black))
   AND EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (41, 42))
   AND EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (man))
person rMX    schedule 17.11.2015
comment
Это тоже работает, спасибо! Как насчет COUNT(shopitem.id)? - person fsasvari; 17.11.2015
comment
Не понимаю, что вы имеете в виду. Использование COUNT(shopitem.id) вернет количество элементов, соответствующих фильтрам. - person rMX; 17.11.2015
comment
К сожалению, это работает и для COUNT(id)! Я использовал $stmt-›fetchColumn(); вместо $stmt-›rowCount(); в PDO, и он вернул неправильное количество строк... - person fsasvari; 18.11.2015