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.

Answer 1

I've come up with an alternative, possibly more understandable approach. Let's look at the problem step by step in a simple example. Our initial tables can have the following data:

objects table:

object_id  |  object_group_id
    1                1
    2                1
    3                1
    4                2
    5                1
    6                1

attributes table:

attr_id | attr_object_id  |  attr_property_id  |  attr_value
    1           1                   1                50000
    2           1                   1                75000
    3           1                   1               150000
    4           1                   2                 1000
    5           1                   2                 5000
    6           2                   1                30000
    7           2                   1               200000
    8           2                   2                 7000
    9           3                   1               500000
   10           3                   2                 1000
   11           4                   1                90000
   12           4                   2                 6000
   13           5                   1               150000
   14           5                   2                 3000
   15           6                   1                70000
   16           6                   2                 1000

I. We start working with the second table since the main part of the problem is actually in it, and we apply our filter directly as:

SELECT * from attributes
WHERE (attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000)

Notice that we use an "OR" between conditions since we need to get all the rows from the attributes table that apply to one OR another condition

The result is the following:

attr_id | attr_object_id  |  attr_property_id  |  attr_value
    1           1                   1                50000
    2           1                   1                75000
    5           1                   2                 5000
    6           2                   1                30000
    8           2                   2                 7000
   11           4                   1                90000
   12           4                   2                 6000
   14           5                   2                 3000
   15           6                   1                70000

II. Now we need to take only those attr_object_id that have both attr_property_id "1" and "2" in the above result, i.e. those attr_object_id adhere to our initial problem's filter. We can achieve that by the following query:

SELECT attr_object_id, count(distinct(attr_property_id)) FROM attributes
WHERE (attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000)
GROUP BY attr_object_id

The result is:

attr_object_id  |  count
      1              2
      2              2
      4              2
      5              1
      6              1

You can see from the above result that attr_object_id with "1", "2" and "4" satisfy both our initial problem's filters, but "5" and "6" are only one of the filters. Let's filter out "5" and "6" now:

SELECT attr_object_id FROM attributes
WHERE (attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000)
GROUP BY attr_object_id
HAVING count(distinct(attr_property_id)) = 2

And we're getting:

attr_object_id
      1
      2
      4

III. At this point, the main part of the problem is solved and we only need to apply the filter from the objects table, i.e. object_group_id = 1. The query for this one is straightforward, we just need to INNER JOIN objects and attributes tables and add a condition to the WHERE clause:

SELECT attr_object_id FROM attributes
INNER JOIN objects on attributes.attr_object_id = objects.object_id
WHERE object_group_id = 1 AND ((attr_property_id = 1 AND attr_value <= 100000) OR (attr_property_id = 2 AND attr_value > 2000))
GROUP BY attr_object_id
HAVING count(distinct(attr_property_id)) = 2

The final result for our example is:

attr_object_id
      1
      2

Answer 2

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.

Answer 3

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';