Rails:加入多个条件 [英] Rails: join with multiple conditions
问题描述
我有一个简单的模型,比如
I have a simple model like
class Interest < ActiveRecord::Base
has_and_belongs_to_many :user_profiles
end
class UserProfile < ActiveRecord::Base
has_and_belongs_to_many :interests
end
当我想查询具有特定兴趣的所有用户时,这很简单
When I want to query all the users with a specific interests, that's fairly simple doing
UserProfile.joins(:interests).where('interests.id = ?', an_interest)
但是我如何寻找具有多种兴趣的用户?当然如果我这样做
But how can I look for users who have multiple interests? Of course if I do
UserProfile.joins(:interests).where('interests.id = ?', an_interest).where('interests.id = ?', another_interest)
我总是得到一个空结果,因为在加入之后,没有行可以同时具有interest.id = an_interest 和interest.id = another_interest.
I get always an empty result, since after the join, no row can have simultaneously interest.id = an_interest and interest.id = another_interest.
ActiveRecord 中有没有办法表达我想要有 2 个(指定的)兴趣相关联的用户列表?
Is there a way in ActiveRecord to express "I want the list of users who have 2 (specified) interests associated?
更新(解决方案)这是我提出的第一个工作版本,感谢 Omar Qureshi
update (solution) that's the first working version I came up, kudos to Omar Qureshi
specified_interests.each_with_index do |i, idx|
main_join_clause = "interests_#{idx}.user_profile_id = user_profiles.id"
join_clause = sanitize_sql_array ["inner join interests_user_profiles interests_#{idx} on
(#{main_join_clause} and interests_#{idx}.interest_id = ?)", i]
relation = relation.joins(join_clause)
end
推荐答案
in (?) 不好 - 它是一个类似 OR 的表达式
in (?) is no good - it's an OR like expression
您需要做的是手写多个连接
what you will need to do is have multiple joins written out longhanded
profiles = UserProfile
interest_ids.each_with_index do |i, idx|
main_join_clause = "interests_#{idx}.user_profile_id = user_profiles.id"
join_clause = sanitize_sql_array ["inner join interests interests_#{idx} on
(#{main_join_clause} and interests_#{idx}.id = ?)", i]
profiles = profiles.join(join_clause)
end
profiles
您可能需要更改 main_join_clause 以满足您的需要.
You may need to change the main_join_clause to suit your needs.
这篇关于Rails:加入多个条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!