Rails 查询 has_many :through 有条件地使用多个 id [英] Rails query a has_many :through conditionally with multiple ids

问题描述

我正在尝试通过 LocationFeature 模型为具有位置和功能的网站构建过滤系统.基本上它应该做的是根据特征 ID 的组合为我提供所有位置.

I'm trying to build a filtering system for a website that has locations and features through a LocationFeature model. Basically what it should do is give me all the locations based on a combination of feature ids.

例如，如果我调用该方法:

So for example if I call the method:

Location.find_by_features(1,3,4)

它应该只返回具有所有所选特征的位置.因此，如果一个位置具有 feature_ids [1, 3, 5]，它不应该被返回，但如果它有 [1, 3, 4, 5] 它应该.但是，目前它给我的位置具有其中一个.所以在这个例子中，它返回两者，因为它们中的每一个都存在一些 feature_id.

It should only return the locations that have all of the selected features. So if a location has the feature_ids [1, 3, 5] it should not get returned, but if it had [1, 3, 4, 5] it should. However, currently it is giving me Locations that have either of them. So in this example it returns both, because some of the feature_ids are present in each of them.

这是我的模型:

class Location < ActiveRecord::Base
  has_many :location_features, dependent: :destroy
  has_many :features, through: :location_features

  def self.find_by_features(*ids)
    includes(:features).where(features: {id: ids})
  end
end

class LocationFeature < ActiveRecord::Base
  belongs_to :location
  belongs_to :feature
end

class Feature < ActiveRecord::Base
  has_many :location_features, dependent: :destroy
  has_many :locations, through: :location_features
end

显然，这段代码没有按照我想要的方式工作，我就是无法理解它.我也试过这样的事情:

Obviously this code isn't working the way I want it to and I just can't get my head around it. I've also tried things such as:

Location.includes(:features).where('features.id = 5 AND features.id = 9').references(:features)

但它什么也不返回.使用 OR 而不是 AND 再次给我.我也试过:

but it just returns nothing. Using OR instead of AND give me either again. I also tried:

Location.includes(:features).where(features: {id: 9}, features: {id: 1})

但这只是给了我 feature_id 为 1 的所有位置.

but this just gives me all the locations with the feature_id of 1.

查询与所有请求的特征匹配的位置的最佳方法是什么?

What would be the best way to query for a location matching all the requested features?

推荐答案

我会将其作为一组子查询来执行.如果您愿意，您实际上也可以将其用作范围.

I would do this as a set of subqueries. You can actually also do it as a scope if you wish.

scope :has_all_features, ->(*feature_ids) {
  where( ( ["locations.id in (select location_id from location_features where feature_id=?)"] * feature_ids.count).join(' and '), *feature_ids)
}

这篇关于Rails 查询 has_many :through 有条件地使用多个 id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！