PostgreSQL условное соединение и дизъюнкция в одном запросе

Question

Как создать запрос, который выбирает продукты с заданными характеристиками, в которых оператор функции формируется условием "и" или "или" в зависимости от группы, к которой они принадлежат?

Описание ситуации

1. Есть магазин с продуктами.
2. Продукты могут иметь функции или нет.
3. Покупатель ищет специфические особенности продуктов, что означает заполнение формы и отправку множества идентификаторов функций.
4. В базе данных каждая функция принадлежит только одной группе функций.
5. Первая группа (атрибут disjunction true, называется "ИЛИ") позволяет отображать продукт, если одна из функций соответствует какой-либо функции, предоставленной клиентом.
   Пример: выбор форм: круг, квадрат, треугольник отображает продукты, которые представляют собой круги, квадраты или треугольники.
6. Вторая группа (атрибут disjunction false, называемый "И") позволяет отображать продукт, только если продукт обладает всеми функциями, представленными клиентом.
   Пример: выбор цвета: красный, зеленый, синий отображает продукты красного, зеленого и синего цвета.

Тестовая среда

http://sqlfiddle.com/

Запрос "ИЛИ"
Это работает за исключением тех продуктов, которые не имеют никаких функций.

SELECT product_id 
FROM product_features 
WHERE product_features.feature_id IN ( 
    SELECT feature_id FROM features 
    LEFT JOIN feature_groups 
        ON features.feature_group_id = feature_groups.feature_group_id 
    WHERE feature_id IN (11, 12, 13) AND feature_groups.disjunction = TRUE 
)
GROUP BY product_id

Запрос "И"
Этот запрос не может быть использован, потому что число функций, для которых дизъюнкция ложна, неизвестно.

SELECT product_id FROM product_features
WHERE feature_id IN (43, 53, 63)
GROUP BY product_id
HAVING COUNT(DISTINCT feature_id) = 3

Answer 1

Случай "ИЛИ"

Проще и быстрее:

SELECT DISTINCT pf.product_id 
FROM   product_features    pf
LEFT   JOIN features       f  USING (feature_id)
LEFT   JOIN feature_groups fg USING (feature_group_id)
WHERE (f.feature_id = ANY (_my_arr) 
AND    fg.disjunction)
OR     _my_arr  = '{}';

... где _my_arr может быть '{11, 12, 13}'::int[] или же '{}'::int[], Если _my_arr было бы NULL использование _my_arr IS NULL вместо.

• Из-за приоритета оператора AND связывает перед OR и скобки не требуются. Они могут улучшить читаемость, хотя.

• DISTINCT или же GROUP BY.. либо здесь хорошо

• AND fg.disjunction.. так как это логический тип, вы можете сократить синтаксис.

• СОЕДИНЕНИЯ, как правило, быстрее, чем другие IN пункт.

• USING это просто обозначение, которое работает с вашим (полезным!) соглашением об именах.

Или даже быстрее (для более чем 1 функции) - и проще разделить случаи:

SELECT product_id
FROM   products p
WHERE  EXISTS (
   SELECT 1
   FROM   product_features pf
   JOIN   features         f  USING (feature_id)
   JOIN   feature_groups   fg USING (feature_group_id)
   WHERE  pf.product_id = p.product_id
   AND    f.feature_id = ANY (_my_arr)
   AND    fg.disjunction
   )
OR     _my_arr  = '{}';

Я бы предпочел разделить случай в вашем приложении в зависимости от ввода (с функциями / без функций). Тривиально делать со второй формой.

Случай "И"

Это классический случай реляционного деления. Мы собрали целый арсенал методов запросов для решения этой проблемы:
Как отфильтровать результаты SQL в сквозном отношении

Может быть:

SELECT product_id
FROM   product_features p1
JOIN   product_features p2 USING (product_id)
JOIN   product_features p3 USING (product_id)
...
WHERE  p1.feature_id = 43
AND    p2.feature_id = 53
AND    p3.feature_id = 63
...

Я игнорирую NOT feature_groups.disjunction в примере, так как это не в вопросе. Добавьте это, если вам это нужно.
Я бы выбрал действительный feature_id перед построением запроса.

-> Демоверсия SQLfiddle.