筛选具有多个条件的EAV表 [英] Filtering EAV table with multiple conditions

问题描述

我有2张桌子:

表objects:

object_id | object_group_id

表attributes:

attr_id | attr_object_id | attr_property_id | attr_value

现在，我想获取所有object_id其中的object_group_id = 1并过滤两个属性:

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.

推荐答案

经过几个小时的组合和尝试，我终于做到了:

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

我太近了，通过将所有过滤条件移到INNER JOIN来完成此操作.

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

这篇关于筛选具有多个条件的EAV表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！