Let's say we have shirts in our database. All shirts have size and color (and many other options, but for now I'm trying to figure it out with two).
The table is something like:
+-------------------------+--------------+--------------+-----------+
| variation_attributes_id | variation_id | attribute_id | option_id |
+-------------------------+--------------+--------------+-----------+
| 1 | 1 | size | s |
| 2 | 1 | color | red |
| 3 | 2 | size | m |
| 4 | 2 | color | red |
| 5 | 3 | size | s |
| 6 | 3 | color | green |
| 7 | 4 | size | m |
| 8 | 4 | color | green |
+-------------------------+--------------+--------------+-----------+
How to select unique variation_id. For example where size is S and color is Green should return variation_id == 3.
You could do that with a simple JOIN (self-join, meaning you join the table with itself under specific conditions). Assuming your table is named shirts, it goes like this:
SELECT DISTINCT s1.variation_id
FROM shirts s1
JOIN shirts s2 ON s1.variation_id = s2.variation_id
WHERE (s1.attribute_id = 'size' AND s1.option_id='s')
AND (s2.attribute_id = 'color' AND s2.option_id='green')
It will return all variation_id that have the color and size that you need, filtering out all other variation_id's.
This query will work fine and it's performance will be OK if you have and index like (variation_id, attribute_id, option_id)
What is more, this query lets you get variation_ids with more complicated arguments, which is much harder using HAVING COUNT(*) = ?.
For example - you need to find all variation_ids that have a size 's' or 'm', and color of 'green' or 'yellow'
SELECT DISTINCT s1.variation_id
FROM shirts s1
JOIN shirts s2 ON s1.variation_id = s2.variation_id
WHERE (s1.attribute_id = 'size' AND s1.option_id IN ('s', 'm'))
AND (s2.attribute_id = 'color' AND s2.option_id IN ('green', 'yellow'))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments