PostgreSQL conditional conjunction and disjunction in one query
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
There is a store with products.
Products may have features or not.
A customer looks for specific features of products which means filling out
the form and sending an array of feature ids.
In the database, each feature belongs to only one group of features.
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.
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
No comments:
Post a Comment