Rails的查询返回的用户属于任何城市及不属于任何城市

Question

我有表Many to Many Associations两者之间：对于防爆​​用户&城市

users 
id name 
1 Bob 
2 Jon 
3 Tom 
4 Gary 
5 Hary 

cities 
id  name 
1  London 
2  New-york 
3  Delhi 

users_cities 
id user_id city_id 
1 1   2 
2 2   1 
3 3   1 
4 3   2 
5 4   3 

我想两个SQL查询

查询其接受city_id数组并返回属于该城市的所有用户。 对于防爆时city_id：[1，2]然后结果应该是 O/P应是

id name 
    1 Bob 
    2 Jon 
    3 Tom 

查询它接受city_id的阵列，并返回所有谁不属于这些城市的用户。 对于防爆时city_id：[1，2]然后结果应该是 O/P应是

id name 
    4 Gary 
    5 Hary 

注： - 我使用

user.rb

has_and_belongs_to_many :cities 

city.rb

has_and_belongs_to_many :users 

Answer 1

基本盟友需要两种方法/作用域

class User < ActiveRecord::Base 
    has_and_belongs_to_many :cities 

    scope :by_cities, ->(city_ids) {includes(:cities).where(cities: {id: city_ids}).distinct} 

    # There are several ways to do that 
    # 1. That will return all not associated records but that we won't need 
    # In this scenario, You need OR condition like city_ids OR NULL 
    # This will return => ["Hary"] 
    scope :not_by_cities, -> {includes(:cities).where(cities: {id: nil})} 

    # 2. Need to create a scope in City model 
    # scope :city_ids, -> all.pluck(:id) 
    # This will return => ["Gary", "Hary"] 
    scope :not_by_cities, -> {includes(:cities).where(cities: {id: [City.city_ids - city_ids, nil]})} 

    # 3. If you are on Rails 5, It is much more easier 
    # This will return => ["Gary", "Hary"] 
    scope :not_by_cities, -> {includes(:cities).where.not(cities: {id: city_ids}).where(cities: {id: nil})} 
end 

选项2

class City < ActiveRecord::Base 
    has_and_belongs_to_many :cities 
    scope :city_ids, -> {all.pluck(:id)} 
end 

结果：

>> User.by_cities([1,2]).pluck(:name) 
=> ["Bob", "Jon", "Tom"] 

>> User.not_by_cities.pluck(:name) 
=> ["Gary", "Hary"] 

如果您是Rails的4.x和仍然需要一些简单的解决方案。使用的有

• Squeel
• ActiverecordAnyOf
• SmartTuple
• Arel任何人。

希望这会对你有帮助。