扶手：联同多个条件 [英] Rails: join with multiple conditions

问题描述

我有一个像

一个简单的模型

 阶级利益和LT;的ActiveRecord :: Base的
  has_and_belongs_to_many：user_profiles
结束

一流的用户配置＆LT;的ActiveRecord :: Base的
  has_and_belongs_to_many：利益
结束
 

当我要查询所有用户与特定的利益，这是非常简单的做

  UserProfile.joins（：利益）。凡（'？interests.id ='，an_interest）
 

但我怎么能找谁拥有多个用户的利益？当然，如果我这样做

  UserProfile.joins（：利益）。凡（'？interests.id ='，an_interest）。凡（'？interests.id ='，another_interest）
 

我总是得到一个空的结果，因为在加入后，没有行可以同时interest.id = an_interest和interest.id = another_interest。

有没有办法在ActiveRecord的能恩preSS我想谁拥有2（指定）的利益相关联的用户列表？

更新（解决方案）这就是我来到了第一个工作版本，荣誉给奥马尔·库雷希

  specified_interests.each_with_index做|我，IDX |
      main_join_clause =_利益＃{IDX} .user_profile_id = user_profiles.id
      join_clause = sanitize_sql_array [内部连接interests_user_profiles的利益_＃{IDX}
                    （＃{main_join_clause}和利益_＃{IDX} .interest_id =？），我]

      关系= relation.joins（join_clause）
    结束
 

（？）

解决方案

在没有好 - 这是一个或像前pression

你需要做的是有多个连接写出longhanded

 型材=用户配置
interest_ids.each_with_index做|我，IDX |
  main_join_clause =_利益＃{IDX} .user_profile_id = user_profiles.id
  join_clause = sanitize_sql_array [内部联接利益的基础上的利益_＃{IDX}
                        （＃{main_join_clause}和利益_＃{IDX} .ID =？），我]
  型材= profiles.join（join_clause）
结束
型材
 

您可能需要更改main_join_clause，以满足您的需求。

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)

I get always an empty result, since after the join, no row can have simultaneously interest.id = an_interest and interest.id = another_interest.

Is there a way in ActiveRecord to express "I want the list of users who have 2 (specified) interests associated?

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 (?) 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

You may need to change the main_join_clause to suit your needs.

这篇关于扶手：联同多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！