Как реализовать поиск элементов по критериям?

Question

[Lordao]

Есть три таблицы - Item, Detail и DetailsItem. Между собой они создают связь many-to-many. Каким образом можно реализовать поиск Item по определенным Detail.

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

Также по этим параметрам могут находиться другие элементы, которые содержат в себе железо, дерево и медь, но если один из параметров не совпадает, то элемент должен не отображаться в итоговой выборке.

SELECT item.idItem, detail.idDetail, detail.title FROM item 
JOIN details_item ON item.idItem = details_item.idItem 
JOIN detail ON detail.idDetail = details_item .idDetail 
WHERE detail.title in ("Железо","Дерево","Медь")

Данный запрос находит элементы в которых есть эти детали, но так же отображаются другие элементы, которые имеют совпадение только 1 из 3.

Comments

[alexalexes]

Вам нужно создать такой подзапрос, чтобы перечень detail.title из нескольких строк для каждого item.idItem свести в одно свойство, чтобы сгруппированная запись выглядела так:
item.idItem, grouping_title
1, 'Железо#Дерево#Медь'
А в основном теле запроса в where проверять,
grouping_title like '%Железо%'
and grouping_title like '%Дерево%'
and grouping_title like '%Медь%'
PS: На Оракле такой подзапрос делается с помощью функции LISTAGG(), в каждой конкретной среде SQL есть своя реализация или велосипед. Могу накидать решение для Оракла.

Answer 1

[Vitsliputsli]

Примерно так:

SELECT item.title FROM (
  SELECT 
        item.idItem
    FROM item 
    JOIN details_item ON item.idItem = details_item.idItem 
    JOIN detail ON detail.idDetail = details_item .idDetail
    GROUP BY item.idItem
    HAVING  sum(CASE 
        WHEN detail.title in ("Железо","Дерево","Медь") 
        THEN 1 
        ELSE 0 END) = 3
) t
JOIN item ON item.idItem = t.idItem

Comments

[Lordao]

Спасибо, то что нужно

Answer 2

[alexalexes]

Можно реализовать, но текст запроса нужно конструировать динамически отдельно для конкретного набора обязательных критериев:

select item.idItem
  from item
 where
count (SELECT details_item.idItem FROM details_item 
JOIN detail ON detail.idDetail = details_item .idDetail 
WHERE detail.title = "Железо"
  and details_item.idItem = item.idItem) > 0
  
  and 
  
  count (SELECT details_item.idItem FROM details_item 
JOIN detail ON detail.idDetail = details_item .idDetail 
WHERE detail.title = "Дерево"
  and details_item.idItem = item.idItem) > 0

  and 
  
count (SELECT details_item.idItem FROM details_item 
JOIN detail ON detail.idDetail = details_item .idDetail 
WHERE detail.title = "Медь"
  and details_item.idItem = item.idItem) > 0
  
  .... и так далее, все обязательные критерии прописываем через подзапрос count()

Вариант 2 с нечувствительностью к соответствию одному из параметров поиска:

select idItem
from (select item.idItem,
       count (SELECT details_item.idItem FROM details_item 
                JOIN detail ON detail.idDetail = details_item .idDetail 
               WHERE detail.title = "Железо"
                 and details_item.idItem = item.idItem) as count_1,
  
       count (SELECT details_item.idItem FROM details_item 
                JOIN detail ON detail.idDetail = details_item .idDetail 
               WHERE detail.title = "Дерево"
                 and details_item.idItem = item.idItem) as count_2,
       
       count (SELECT details_item.idItem FROM details_item 
                JOIN detail ON detail.idDetail = details_item .idDetail 
               WHERE detail.title = "Медь"
                 and details_item.idItem = item.idItem) as count_3
                 
                .... и так далее все обязательные критерии через подзрапрос count(...) as count_n 
  from item)  
  where count_1 > 0 and count_2 > 0 and count_3 > 0 -- строгое соответствие параметрам поиска
     -- тут делаем логику нечувствительности к одному из параметров поиска
     or count_1 = 0 and count_2 > 0 and count_3 > 0
     or count_1 > 0 and count_2 = 0 and count_3 > 0
     or count_1 > 0 and count_2 > 0 and count_3 = 0