-1

-The question itself might not describe the issue well, but I'm not sure how to present it

I have implemented this design ,so I have the following table diagram.

 +---------------+     +-------------------+
 | PRODUCTS      |-----< PRODUCT_VARIANTS  |
 +---------------+     +-------------------+
 | #product_id   |     | #product_id       |
 |  product_name |     | #variant_id       |
 +---------------+     |  price            |
         |             +-------------------+
         |                       |
+--------^--------+     +--------^--------+
| PRODUCT_OPTIONS |-----< VARIANT_VALUES  |
+-----------------+     +-----------------+
| #product_id     |     | #product_id     |
| #option_id      |     | #variant_id     |
+--------v--------+     | #option_id      |
         |              |  option_value_id|
+-----------------+     +--------v--------+
| OPTIONS         |              |
+-----------------+              |
| #option_id      |              |
|  option_name    |              |
+-----------------+              |
         |                       |
 +-------^---------+             |
 | OPTION_VALUES   |-------------+
 +-----------------+
 | #option_id      |
 | #option_value_id|
 |  value_name     |
 +-----------------+

fields with "#" are the Primary Keys

I want to afford users to filter products using option_values. say I have the following product variant shirtA(product) size(option) small(option_value), color(option) green(option_value), and a user would like to filter for all products that have are say small and red shirts, so the previous product variant should not be included.

I have tried doing this

SELECT p.product_id, p.title, p.description FROM product_variants pv
JOIN products p ON pv.product_id = p .product_id
JOIN variant_values vv ON vv.product_variant_id = pv.product_variant_id
WHERE vv.option_value_id IN (6, 10)
GROUP BY (p.product_id, p.title, p.description);

but as you can see it includes the products that hold option_value_id 6 or 10 and not both, so how to remove products that don't have both option_values not just one?

UPDATE 1

to clarify what I need to do I want is : enforce all products selected to have all the option value ids the user sends (in a form of an array) WHERE vv.option_value_id IN (6, 10). the idea is just like in most of e-commerce websites when user filters for his/her preferences option_values, so user might want to search for all products that are large (let's say that is option_value_id "6") and red (option_value_id "10") and maybe green... and so on. so I don't want any product that only has a "red" color but doesn't have "large" size to be returned, and if user added another say color to the filter say "green" color then all the returned products should match all three filters (having green and red colors and large size).

Omar Abdelhady
  • 1,528
  • 4
  • 19
  • 31

3 Answers3

2

You want a having clause. Your question is unclear. But if you want both, then use:

SELECT p.product_id, p.title, p.description
FROM product_variants pv JOIN
     products p 
     ON pv.product_id = p .product_id JOIN
     variant_values vv
     ON vv.product_variant_id = pv.product_variant_id
WHERE vv.option_value_id IN (6, 10)
GROUP BY p.product_id, p.title, p.description
HAVING COUNT(DISTINCT option_value_id) = 2;

If you want just one, you can change the 2 to 1.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

If dynamic SQL building is an option for you, you may use INTERSECT for filtering product variants:

SELECT p.product_id, p.title, p.description 
FROM product_variants pv
  JOIN products p ON pv.product_id = p .product_id
  JOIN (SELECT variant_id
        FROM variant_values
        WHERE (option_value_id = 6)
        INTERSECT 
        SELECT variant_id
        FROM variant_values
        WHERE (option_value_id = 10)
    ) as vv ON vv.product_variant_id = pv.product_variant_id;

This might not be optimal query if you plan very large numbers of product variants and their options, but still the query is simple enough to try.

Julius Tuskenis
  • 1,323
  • 8
  • 13
1

Since you already have a predefined set of ids, perhaps you can make use of an array here.

SELECT p.product_id, p.title, p.description FROM product_variants pv
JOIN products p ON pv.product_id = p .product_id
JOIN variant_values vv ON vv.product_variant_id = pv.product_variant_id
WHERE vv.option_value_id IN (6, 10)
GROUP BY (p.product_id, p.title, p.description)
HAVING array_agg(distinct vv.option_value_id order by vv.option_value_id) = array[6,10];
Joachim Nielandt
  • 145
  • 8
  • 1
    `array(6,10)` is an invalid array literal. You have to use `array[6,10]` –  Mar 22 '21 at 06:44
  • it was complaining that it could not cast bigInt to Int so I added this ```HAVING array_agg(distinct vv.option_value_id) = ARRAY[6::bigint,10::bigint];``` but I am not sure what is going on here can you please clarify why did you use ```array_agg and distinct``` – Omar Abdelhady Mar 22 '21 at 06:59
  • @OmarAbdelhady: you could also use `array[6,10]::bigint[]` –  Mar 22 '21 at 07:36
  • @a_horse_with_no_name thanks, updated that, wrote that line from memory so mistook the syntax :) – Joachim Nielandt Mar 22 '21 at 14:24
  • 1
    @OmarAbdelHady I slightly modified the answer still. I'll try to explain the array_agg+distinct. For each group, doing the `distinct option_value_id order by option_value_id` makes sure you get a nice list of id's, sorted, never repeating. This list needs to be aggregated into a single value, otherwise you can't use it as a filter. That's what array_agg does: it distills the list into a single `array`. That array can be compared by you, using `= array[6,10]`. – Joachim Nielandt Mar 22 '21 at 14:31
  • 1
    However, @gordon-linoff 's answer seems more succinct, more performant, and equally correct. – Joachim Nielandt Mar 22 '21 at 14:39
  • Thanks a lot, I will try his answer keeping yours in mind, I also updated the question as it might have not been clear. – Omar Abdelhady Mar 22 '21 at 15:53