Как организовать сложный запрос в SQL?

Question

[alst161]

Выбился из сил. Как реализовать следующее:
есть таблица "product_attribute" следующей структуры (с мини примером):
product_id attribute_id text
105 5 100
105 8 55
105 9 92
106 5 100
106 8 95
106 9 93
107 5 100
107 8 55
107 9 80

и таблица с категориями "product_category"
product_id category_id
105 8
106 8
107 8

Нам надо получить фильтр, где пользователь указывает категорию и несколько атрибутов (например: категория - 8, выбранные атрибуты attribute_id 5 с text 100 и attribute_id 8 с text 55. надо выбрать оставшиеся варианты атрибутов в данном случае должно вернуть attribute_id=9, text=92 и attribute_id=9, text=80
ещё поясню: это фильтр шин на сайте, где используются такие атрибуты как радиус, ширина, высота, сезонность. фильтр работает ajax. каждый раз при выборе одного из селекторов, он обновляет не выбранные подгружая только допустимые значения
какие есть мысли?

Comments

[mletov]

Понимаю, что правильный вопрос уже дан, чисто для себя хотел решить эту задачу, но не до конца понял условие. Ведь при таких вводных данных должны вернуться "9 92, 8 95, 9 93, 9 80", почему "8 95, 9 93" в выборку не попадают?

[alst161]

mletov: потому что в условии атрибут 5 должен быть равен 100 и 8 = 55, такому условию соответствуют товары только 105 и 107. вот их оставшиеся атрибуты мы и выводим, т.е. 9=80 и 9=92

[mletov]

alst161: Спасибо, не сразу понял задумку

Answer 1

[Rsa97]

Как-то так:

SELECT DISTINCT `a`.`attribute_id`, `a`.`text`
  FROM (
    SELECT DISTINCT `p`.`id`
      FROM `product` AS `p`
      JOIN `product_category` AS `c` ON `c`.`product_id` = `p`.`id` 
        AND `c`.`category_id` = :categoryId
      JOIN `product_attribute` AS `a1` ON `a1`.`product_id` = `p`.`id`
        AND `a1`.`attribute_id` = :attribute1 AND `a1`.`text` = :attrValue1
      JOIN `product_attribute` AS `a2` ON `a2`.`product_id` = `p`.`id`
        AND `a2`.`attribute_id` = :attribute2 AND `a2`.`text` = :attrValue2
      ...
      JOIN `product_attribute` AS `aN` ON `aN`.`product_id` = `p`.`id`
        AND `aN`.`attribute_id` = :attributeN AND `aN`.`text` = :attrValueN
  ) AS `pr`
  JOIN `product_attribute` AS `a` ON `a`.`product_id` = `p`.`id`
  WHERE `a`.`attribute_id` NOT IN (:attribute1, :attribute2,... attributeN)
  ORDER BY `a`.`attribute_id, `a`.`text`

Comments

[alst161]

зачем трогать таблицу product?

[Rsa97]

alst161: Да в принципе необязательно, на автомате приписал. Можно и так (подзапрос):

SELECT DISTINCT `c`.`product_id`
      FROM `product_category` AS `c` 
      JOIN `product_attribute` AS `a1` ON `a1`.`product_id` = `c`.`product_id`
        AND `a1`.`attribute_id` = :attribute1 AND `a1`.`text` = :attrValue1
      ...
      WHERE `c`.`category_id` = :categoryId

[alst161]

Rsa97: всё хорошо. спасибо за помощь, только один момент: ошибка тут ON `a`.`product_id` = `p`.`id` вместо `p`.`id` надо `pr`.`id`. а так спасибо большое)

Answer 2

[Марат]

select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb
where p.product_id = pa.product_id and p.category_id=8 and
pa.product_id  not in (
  select pa2.product_id from product_attribute pa2
     where (pa2.attribute_id=9 and pa2.text=92) or (pa2.attribute_id=8 and pa2.text=55)
) and
not((pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and  pa.text=55))

Выражения по типу (pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and pa.text=55) надо формировать динамически или

Создать третью таблицу filtr_sess (sid, attribute_id, text) куда перед вызовом закидывать текущую сессию + выбранные атрибуты. Тогда вызов можно осуществлять только по Sid-у и Category_Id.
Примерно так (пишу без доступа к СУБД с чужого ПК, прошу не винить в синтаксисе):

select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb, filtr_sess fs
where p.product_id = pa.product_id and p.category_id=8 and
pa.product_id  not in (
select pa2.product_id from product_attribute pa2, filtr_sess s
     where (pa2.attribute_id=s.id and pa2.text=s.text and s.sid=X)
) and fs.sid=X and pa.attribute_id <> fs.id and pa.text <> fs.text

Comments

[alst161]

спасибо за ответ, не проверял. первый больше подошёл моей душе) более понятно и проще