用于返回用户的Rails查询属于任何城市&不属于任何城市 [英] Rails Query to return users belongs to any cities & not belong to any cities

问题描述

我在两个表之间有对许多协会：对于前用户&城市

I have Many to Many Associations between two tables: For Ex users & cities

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查询

对于Ex，当city_id为[1，2]时，结果应为
，O / P应为

Query which accepts array of city_id and return all the users belongs to that cities. For Ex when city_id : [1, 2] then result should be O/P should be

   id  name
    1   Bob
    2   Jon
    3   Tom

该查询接受city_id数组，并返回所有不属于那些城市的用户。例如，当city_id为[1，2]时，结果应为
O / P应为

Query which accepts array of city_id and return all the users who do not belong to those cities. For Ex when city_id : [1, 2] then result should be O/P should be

    id  name
    4   Gary
    5   Hary

注意：-我正在使用

user.rb

has_and_belongs_to_many :cities

city.rb

has_and_belongs_to_many :users

推荐答案

基本上，您需要两个方法/作用域

Basically you need two methods/scopes

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

For Option 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并仍然想要一些简单的方法解。使用其中的任何人

If You are Rails 4.x and still want some easy solution. Use anyone of there

• Squeel


• ActiverecordAnyOf


• SmartTuple


• Arel 。

• Squeel
• ActiverecordAnyOf
• SmartTuple
• Arel.

希望这会对您有所帮助。

Hope this will help you.

这篇关于用于返回用户的Rails查询属于任何城市&不属于任何城市的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！