PostgreSQL conditional conjunction and disjunction in one query

Question

How to create a query which selects products of given features where feature statement is formed by "and" or "or" condition depending on a group they belong to?

Description of the situation

1. There is a store with products.
2. Products may have features or not.
3. A customer looks for specific features of products which means filling out the form and sending an array of feature ids.
4. In the database, each feature belongs to only one group of features.
5. The first group (disjunction attribute is true, called "OR") allows to display the product if one of the features matches any feature submitted by the customer.
   Example: selecting shapes: circle, square, triangle displays products which are circles or squares or triangles.
6. The second group (disjunction attribute is false, called "AND") allows to display the product only if the product has all of the features submitted by the customer.
   Example: selecting colors: red, green, blue displays products which are red and green and blue.

Test environment

http://sqlfiddle.com/#!12/f4db7

"OR" Query
It works except for those product which have no features.

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

"AND" Query
This query cannot be used because the number of features where disjunction is false is not known.

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

"OR" case

Simpler and faster:

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  = '{}';

... where _my_arr can be '{11, 12, 13}'::int[] or '{}'::int[]. If _my_arr would be NULL use _my_arr IS NULL instead.

• Due to operator precedence AND binds before OR and parentheses are not required. They may improve readability, though.

• DISTINCT or GROUP BY .. either is good here.

• AND fg.disjunction .. since this is a boolean type, you can shorten the syntax.

• JOINs are generally faster than another IN clause.

• USING is just a notational shortcut that works with your (useful!) naming convention.

Or, even faster (for more than 1 feature) - and simpler to split cases:

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  = '{}';

I would rather split the case in your app depending on input (with features / no features). Trivial to do with the second form.

"AND" case

This is a classical case of relational division. We have assembled a whole arsenal of query techniques to deal with it in under this related question:
How to filter SQL results in a has-many-through relation

Could be:

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
...

I am ignoring NOT feature_groups.disjunction in the example since it is not in the question either. Add it if you need it.
I would select valid feature_id before building the query.

-> SQLfiddle demo.