包括所有对象，但仅对活动记录中的关联应用条件

Question

使用以下模型，

class User < ActiveRecord::Base
has_many :addresses
# Has an attribute named active
end
class Address < ActiveRecord::Base
# Has an attribute named city
end

如何在对数据库的单个查询中获取所有usersactive: true并且每个用户仅包含addresses位置city: Foo？

注意：如果用户没有city为"Foo"的地址，则他们仍必须包含在结果中，并带有空addresses。

示例数据

test=# select * from users;
id | name | active 
----+------+--------
1 | Jack | t
2 | John | t
3 | Jane | t
4 | Jill | f
(4 rows)
test=# select * from addresses;
id | user_id |    street    | city 
----+---------+--------------+------
1 |       1 | Foo Street   | Foo
2 |       1 | Bar Street   | Bar
3 |       2 | Bar Street   | Bar
4 |       4 | Foo Street   | Foo
5 |       1 | Foo Street 1 | Foo
(5 rows)

预期成果

id | name |    street    | city 
----+------+--------------+------
1 | Jack | Foo Street 1 | Foo
1 | Jack | Foo Street   | Foo
2 | John |              | 
3 | Jane |              | 
(4 rows)

下面的SQL查询可以产生该结果，但是我想不出一种方法可以让AR生成该查询或任何其他查询来生成上述结果。

select users.id, name, street, city from users left join lateral (
select * from addresses where addresses.user_id = users.id and city = 'Foo'
) addr on true
where users.active;

Answer 1

请尝试 添加Address模型

class Address < ActiveRecord::Base
belongs_to :user  
end

那么你的查询就像

User.joins("LEFT JOIN addresses ON users.id=addresses.user_id").where("users.active=? AND addresses.city=?", true, "FOO")

Answer 2

试试这个

User.joins("LEFT JOIN addresses on addresses.user_id = users.id").where(active: true).select("users.*,if (addresses.city = 'Foo', addresses.city, '') AS 'city',if (addresses.city = 'Foo', addresses.street, '') AS 'street'").order("city").group(:id).uniq(:id)

Answer 3

我想可以用这种方式实现，我希望它可能是正确的：-

User.all.select{|a|a.active == true}.map(&:address).flatten.select{|v|v.city == "Foo" || v.city == ""}

认为可以对其进行修改以获得正确的结果。希望它有所帮助，如果我出错了，请纠正我。