I have the following table (offer_properties) in MYSQL for an e-commerce website:
+----------+-------------+---------+
| offer_id | pkey | pvalue |
+----------+-------------+---------+
| 63 | shoesize | shoe_47 |
| 63 | sport | walking |
| 63 | color | multi |
| 12 | color | multi |
| 12 | shoesize | size_48 |
| 12 | shoesize | size_47 |
| 12 | shoesize | size_46 |
| 12 | sneakertype | comfort |
| 12 | sport | running |
+----------+-------------+---------+
What is the easiest way to find the offers where
shoesize = size_48 AND sport = running
I could do it by
select offer_id from offer_properties where (pkey = "sport" and pvalue = "running") and offer_id IN (select offer_id from offer_properties where (pkey = "shoesize" and pvalue = "size_48"));
However, this recursive approach makes it really difficult as there could be various property match requests. Moreover, I have other tables that I need to JOIN and the queries gets complicated quite quickly. I have one table that holds the values (price, description, etc.) and another normalized table that holds offer tags.
I LEFT JOIN to find the offers that matches certain tags with certain properties and with certain values. However, things get quite complicated very quikly.
What would be your guidance for such scenarios? Should I use simple queries with Promises and use the app logic to filter things step by step?
Many thanks
You could use conditional aggregation to avoid self-joins.
select offer_id
from (
select offer_id,
sum(case when pkey = 'shoesize' and pvalue = 'size_48' then 1 else 0 end) cond1,
sum(case when pkey = 'sport' and pvalue = 'running' then 1 else 0 end) cond2
from offer_properties
group by offer_id
)z
where cond1> 0 and cond2> 0;
| offer_id |
|---|
| 12 |