代码之家 › 专栏 › 技术社区 › Hiasinho

查询具有多个AND和OR条件的联接表

arel ruby-on-rails-4 activerecord postgresql ruby-on-rails

0

Hiasinho · 技术社区 · 12 年前

我需要在Rails中构建一个查询,该查询返回具有特定属性集的人。我简化了我的例子。下面是两张表:

Table: people
+----+----------+
| id |   name   |
+----+----------+
|  1 | Person A |
|  2 | Person B |
|  3 | Person C |
+----+----------+

Table: attributes
+----+-----------+--------+--------+
| id | person_id |  name  | value  |
+----+-----------+--------+--------+
|  1 |         1 | age    | 32     |
|  2 |         1 | gender | male   |
|  3 |         2 | age    | 16     |
|  4 |         2 | gender | male   |
|  5 |         3 | gender | female |
+----+-----------+--------+--------+

person_id 是指桌子上的人 people .

在我的提问中,我想(例如)问以下问题:

给我所有25岁以上的男性!

name = 'gender' AND value = 'male' 和 name = 'age' AND value > '25' 应返回1条记录 (person_id=1)
给我所有男性或25岁以上的人!

name = 'gender' AND value = 'female' 或 name=“age”AND值>'25' 应返回2条记录 (person_id=1 and 3)

例2并不难做,但我对例1有一些问题。我不知道如何处理 AND 在这里不要忘记: WHERE 语句是动态的。意味着可能有很多,或者只有一个。

基本上,我正在寻找正确的SQL语句来实现这一点。我已经玩了一点,到现在为止我得到的最好的东西是:

SELECT people.* 
FROM people 
INNER JOIN attributes ON attributes.person_id = people.id 
WHERE
  attributes.name = 'gender' AND attributes.value = 'male' OR 
  attributes.name = 'age' AND attributes.value > '25' 
GROUP BY people.id 
HAVING count(*) = 2

我不喜欢这个解决方案,因为我必须在 HAVING 条款要做到这一点,必须有一个更优雅、更灵活的解决方案。

下面是一个不起作用的更复杂的示例:

SELECT people.* 
FROM people 
INNER JOIN attributes ON attributes.person_id = people.id 
WHERE
  (attributes.name = 'gender' AND attributes.value = 'male') OR 
  (attributes.name = 'age' AND attributes.value > '25') AND
  (attributes.name = 'bodysize' AND attributes.value > '180')
GROUP BY people.id

任何想法和帮助都将受到赞赏。谢谢

2 回复 | 直到 12 年前

1

pozs 12 年前

考虑以下内容

SELECT people.* 
FROM people
LEFT JOIN attributes AS attr_gender
    ON attr_gender.person_id = people.id 
    AND attr_gender.name = 'gender'
LEFT JOIN attributes AS attr_age
    ON attr_age.person_id = people.id 
    AND attr_age.name = 'age'

结合:

1)

SELECT ...
WHERE attr_gender.value = 'male'
AND attr_age.value::int > 25

2)

SELECT ...
WHERE attr_gender.value = 'male'
OR attr_age.value::int > 25

笔记 :需要铸造- '9' > '25' .

2

0

Quentin Gaillard 12 年前

您是否尝试过:

(attributes.name='gender'和attributes.value='male')或
(attributes.name='age'AND attributes.value>'25')

没有“GROUP BY”/“HAVING”部件?