Question 20161 in Mysql Question posted in
The official MySQL documentation can be found here.

Finding a common ID (value) in a normalized table based on several where clauses – Mysql

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

Tags:

3

Answers


  1. 0

    You can use quite simple self join:

    select offer_id
from offer_properties o1
where pkey = "sport"
  and pvalue = "running"
  and exists (
        select 1
        from offer_properties o2
        where o2.offer_id = o1.offer_id
        and o2.pkey = "shoesize"
        and o2.pvalue = "size_48"
        );

    Or you could use EXIST. It quite similar to your current query.

    select offer_id
from offer_properties o1
join offer_properties o2
on (o2.offer_id = o1.offer_id
        and o2.pkey = "shoesize"
        and o2.pvalue = "size_48")
where pkey = "sport"
  and pvalue = "running";

    Both solutions require additional constructions, but with current model, I believe you have no chance avoiding it.

    On the other hand, I would argue that exists (as your current query) could be written in a single line, and should not clutter your query to much.

    Login or Signup to reply.
  2. 0

    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

    View on DB Fiddle

    Login or Signup to reply.
  3. 0

    No need for joins, nor for subqueries. We can do direct filtering on aggregates with a having clause:

    select offer_id
from offer_properties
group by offer_id
having max(pkey = 'shoesize' and pvalue = 'size_48') = 1
   and max(pkey = 'sport'    and pvalue = 'running') = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search