Get records where join table ids matches or is a subset of a list of ids

Question

I have a table called variants, which is joined to another table called option_values in a many-to-many relationship through a table called option_values_variants.

I would like to pull all the variants which have a specific subset of ids in the joined option_values table. This is easier to demonstrate with an example:

Table data:

variants

id |      name
--------------
 1 | Tennant
 2 | Smith
 3 | Eccleston
 4 | Hurt

option_values

id | name
---------
80 | blue
81 | red
82 | green
83 | 64 gb
84 | 128 gb
85 | 256 gb
86 | short
87 | long

option_values_variants

variant_id | option_value_id
----------------------------
         1 | 80
         1 | 85
         1 | 86
         2 | 82
         2 | 85
         2 | 87
         3 | 80
         3 | 84
         3 | 86
         4 | 81
         4 | 83
         4 | 86

In this example, I want to get all the variants which have option values "blue" and "short", specifically (so option value ids 80 and 86).

The expected result should be variants "Tennant" and "Eccleston", since those variants have both "blue" and "short", despite having one different option value each. "Hurt" has "short" but does not have "blue", so he should be excluded, and "Smith" has neither.

I seem to be having a bit of a brain block in terms of forming this query, either within the Rails context, or in plain SQL. I cannot seem to figure out how to specify that "the join should be made only when the ids of the joining table match or are a subset of the target parameter list". This is my current attempt at the moment:

@product.variants.joins(:option_values).where(option_values: {id: [80, 86]}).uniq

Which produces the SQL query:

SELECT DISTINCT `variants`.* FROM `variants` 
INNER JOIN `option_values_variants` ON `option_values_variants`.`variant_id` = `variants`.`id` 
INNER JOIN `option_values` ON `option_values`.`id` = `option_values_variants`.`option_value_id` 
WHERE `variants`.`product_id` = 32 AND `option_values`.`id` IN (80, 86)

Since the query is performing a WHERE ... IN (), it does not produce the desired result. Using the above example, I would return "Tennant", "Eccleston", and "Hurt", however "Hurt" should not belong because it does not contain the option value "blue" (or option value id 80).

How can I select records where the joined table rows match or are a subset of a given list of ids?

Additionally, it may be a result of said brain block, but admittedly I may not be explaining this very well. Please let me know if I am not making any sense

GROUP BY something HAVING COUNT([DISTINCT] something) = n [where n is the number of items in IN() ] — Strawberry, Mar 10 '15 at 21:53
@Strawberry You are absolutely right. Works perfectly. Thank you for your help! — Paul Richter, Mar 10 '15 at 21:59

score 2 · Accepted Answer · edited Mar 10 '15 at 21:55

2

You can use a group by and specify that the results have both option values with having count(distinct option_values.id) = 2

   SELECT
        v.id,
        v.name
    FROM variants v
        INNER JOIN option_values_variants ov
            ON ov.variant_id = v.id 
        INNER JOIN option_values o
            ON o.id = ov.option_value_id 
    WHERE v.product_id = 32
        AND o.id IN (80, 86)
    GROUP BY
        v.id,
        v.name
    HAVING COUNT(DISTINCT o.id) = 2

edited Mar 10 '15 at 21:55

Strawberry

33,750
13
40
57

answered Mar 10 '15 at 21:52

Ron Smith

3,241
1
13
16

1

That is absolutely 100% the correct answer. I knew I was missing something simple. Thank you for your help. – Paul Richter Mar 10 '15 at 21:59
1

My pleasure. Really. I'm a nerd and I think this stuff is fun... :) – Ron Smith Mar 10 '15 at 22:19

Get records where join table ids matches or is a subset of a list of ids

1 Answers1