Filtering EAV table with multiple conditions

Question

I have 2 tables:

Table objects:

object_id | object_group_id

Table attributes:

attr_id | attr_object_id | attr_property_id | attr_value

Now, I want to get all object_id where object_group_id = 1 and filters two attributes:

(attr_property_id = 1 AND attr_value <= '100000')
   AND 
(attr_property_id = 2 AND attr_value > '2000')

I was trying to construct some queries, like this:

SELECT * FROM objects as o

/* filter1 join */
INNER JOIN 
    attributes AS f1 
        ON 
    o.object_id = f1.attr_object_id 
        AND 
    f1.attr_property_id = 1

/* filter2 join */
INNER JOIN 
    attributes AS f2 
        ON 
    f1.attr_object_id = f2.attr_object_id 
        AND 
    f2.attr_property_id = 2

WHERE 
    o.object_group_id = 1
       AND
   f1.attr_value <= '100000'
       AND
   f2.attr_value > '2000'

... but still can't get what I need.

Do you want to have 1 row with attr_values or 2 row. Please show a sample from the result you want — Bernd Buffen, Oct 29 '15 at 19:56
I want to get one row per one object, wich are matching two key-value conditions in attributes. — WarGasm, Oct 29 '15 at 20:49
look at this. it generates your query as pivot table http://stackoverflow.com/questions/33350524/pivot-a-table-and-display-n-ordered-time-series/33351008?noredirect=1#comment54499334_33351008 — Bernd Buffen, Oct 29 '15 at 21:30

score 4 · Accepted Answer · answered Oct 29 '15 at 21:13

After couple hours of combining and trying, I finally did:

    SELECT * FROM objects as o

/* filter1 join */
INNER JOIN 
    attributes AS f1 
        ON 
    o.object_id = f1.attr_object_id 
        AND 
    f1.attr_property_id = 1
        AND
    f1.attr_value <= '100000'

/* filter2 join */
INNER JOIN 
    attributes AS f2 
        ON 
    f1.attr_object_id = f2.attr_object_id 
        AND 
    f2.attr_property_id = 2
        AND
    f2.attr_value > '2000'

WHERE 
    o.object_group_id = 1

I was too close, and done this by moving all filter conditions to INNER JOIN.

score 0 · Answer 2 · answered Oct 29 '15 at 21:01

Try this. I am not sure why do you have the last lines

SELECT
  o.object_id, o.object_group_id,
  f1.attr_value AS val1,
  f2.attr_value AS val2,
FROM objects AS o
LEFT JOIN attributes f1 ON o.object_id = f1.attr_object_id AND f1.attr_property_id = 1
LEFT JOIN attributes f1 ON o.object_id = f2.attr_object_id AND f2.attr_property_id = 2
WHERE
    o.object_group_id = 1
AND
   f1.attr_value <= '100000'
AND
   f2.attr_value > '2000';

remove this lines and test it also

AND
   f1.attr_value <= '100000'
AND
   f2.attr_value > '2000';

I've tried with `LEFT JOIN`, but it never compares more than one key-value pair. It splits all key-value pairs into seperate rows. So I can't compare both. — WarGasm, Oct 29 '15 at 21:20

Filtering EAV table with multiple conditions

2 Answers2

Linked

Related