Selecting product variants that satisfy all option criteria

Question

My current query:

select `t1`.* from `product_variant_values` as t1 
inner join (
    select `t1`.* from `product_variant_values` as t1
    where (
    (t1.`option_id` = 1 and t1.`value_id` = 1) or 
    (t1.`option_id` = 2 and t1.`value_id` = 4) or 
    (t1.`option_id` = 3 and t1.`value_id` = 7) )
    group by t1.variant_id having count(t1.id) = 3
) t2 on t2.variant_id = t1.variant_id

I have this query, and I want to know how I can get rid of the second select and just use a simple join? Here is a link to a picture of my main table: http://prntscr.com/99sxp8 . I set where condition for option_id - value_id pair and want to select those group of rows which have the same variant_id and number of rows is 3.

Update: I do not know the amount of needed rows. I just have an array of needed arguments:

.

The keys are option_id and the value is value_id. These will differ, and I want to select rows which match the arguments, and the number of rows must be equal to the count of options (or count($arguments)) and all rows must have the same variant_id, which is what I'm looking for.

Update 2: Here is the full picture of my tables.

The products table

The Product variants table

Here stock and price will be real numbers.

The Options table

Also I have option_group table, but it consists of just an id and a name. It is as option set. One product will have only one option group.

The table variantable_option_variants

These are values for options.

The product_variant_values table

This table contains all possible combination for option values and parent variant. One example: "red color - s size - long height", matches option_id/value_id set of (1:1, 2:4, 3:7).

I want to find variant_id (from product_variant_values) by a set of combinations of option_id and value_id.

Update 2: The combination for each option, option_id and value_id really exists. They are created when admin saves the option group.

User have dropdown lists for each of the different options. And when he submits the form, I get the combinations of options_id's and value_id's that should be enough to find the variant_id.

Answer 1

I'm not quite sure I understood your requirements, but as I understand it you want to find the variant id for a set of values with the values 1, 4, 7 for the three options.

In other words, you need to do a double join in order to access the three different values, this can be achieved using some thing like this:

SELECT t1.variant_id, t1.value_id t1v, t2.value_id t2v, t3.value_id t3v 
  FROM product_variant_values as t1
       JOIN product_variant_values AS t2 
         ON t1.variant_id = t2.variant_id AND t2.option_id = 2 AND t1.option_id = 1
       JOIN product_variant_values AS t3 
         ON t1.variant_id = t3.variant_id and t3.option_id = 3

When running this query as is, using a temporary table built from variant_id's up to 7 from http://prntscr.com/99sxp8, I got the following output:

variant_id t1v t2v t3v
1           1   4   7
2           1   4   8
3           1   4   9
4           1   5   7
5           1   5   8
6           1   5   9
7           1   6   7

Notice in this output how I've connected the t1 table to option_id=1, and t2 to option_id=2, and so on. Therefore when adding this line:

 WHERE t1.value_id = 1 AND t2.value_id = 4 AND t3.value_id = 7 

One can limit the query to a given value set:

variant_id t1v t2v t3v
1           1   4   7

In other words, variant_id = 1 is the only one having the combinations of values 1, 4, 7.

Update: Generalisation to multiple options

The method above could be extended to any number of options, but the cost of it expands for each option you add.

Another option for solving this kind of problem on this end, is to make n different selects, and then doing a intersection of matching variant_id's. If your final intersection has only one element, you've found your variant_id, and if no or more than one element you need to handle that accordingly.

Addendum: Ultimate solution

The solution above was what I came up with when focusing on the join part of your question, and your request to remove the second query. When thinking a little more on it, it's the first query you want to remove. Try this one on for size:

SELECT count(*), variant_id FROM product_variant_values
 WHERE (option_id = 1 AND value_id = 1)
    OR (option_id = 2 AND value_id = 4)
    OR (option_id = 3 AND value_id = 7)
GROUP BY variant_id
HAVING COUNT(variant_id) = 3

If you get more than one or none resulting row out of this query, you have a problem. This is also easily extensible, as you just need to change the matching option count on the HAVING COUNT section and add appropriate extra groups on the OR parts.

Answer 2

Let's just look at your subselect:

select `t1`.* from `product_variant_values` as t1
…
group by t1.variant_id having count(t1.id) = 3

That is non-standard SQL, and doesn't actually make sense. Whenever you do a GROUP BY, all of the columns being selected must either be named in the GROUP BY clase (variant_id in this case) or be an aggregate function.

MySQL, being a weird database, allows this illegal query anyway when the global ONLY_FULL_GROUP_BY option is disabled. (If is disabled by default in MySQL < 5.7.5, and on by default in MySQL ≥ 5.7.5.)