如何查找其has_many通过对象包括某个列表的所有对象的记录？ [英] How to find records, whose has_many through objects include all objects of some list?

问题描述

我得到了一个典型的标签和任何对象关系：

I got a typical tag and whatever-object relation: say

class Tag < ActiveRecord::Base
   attr_accessible :name
   has_many :tagazations
   has_many :projects, :through => :tagazations
end

class Tagazation < ActiveRecord::Base
  belongs_to :project
  belongs_to :tag
  validates :tag_id, :uniqueness => { :scope => :project_id }
end

class Project < ActiveRecord::Base
   has_many :tagazations
   has_many :tags, :through => :tagazations
end

这里没什么特别的：每个项目都带有一个或多个标签。

该应用程序具有搜索功能：您可以选择某些标签，而我的应用程序应向您显示所有标有所有提及标签的项目。因此，我得到了必要的tag_id数组，然后陷入了这样一个简单的问题

nothing special here: each project is tagged by one or multiple tags.
The app has a feature of search: you can select the certain tags and my app should show you all projects which tagged with ALL mentioned tags. So I got an array of the necessary tag_ids and then got stuck with such easy problem

推荐答案

要在一个查询中执行此操作， d想利用常见的 double不存在 SQL查询，该查询实际上对所有Y 查找X 。

To do this in one query you'd want to take advantage of the common double not exists SQL query, which essentially does find X for all Y.

在您的情况下，您可以这样做：

In your instance, you might do:

class Project < ActiveRecord::Base
  def with_tags(tag_ids)
    where("NOT EXISTS (SELECT * FROM tags
      WHERE NOT EXISTS (SELECT * FROM tagazations
        WHERE tagazations.tag_id = tags.id
        AND tagazations.project_id = projects.id)
      AND tags.id IN (?))", tag_ids)
  end
end

或者，您可以使用count，group和had，尽管我怀疑第一个版本比较快，但可以随时进行基准测试：

Alternatively, you can use count, group and having, although I suspect the first version is quicker but feel free to benchmark:

def with_tags(tag_ids)
  joins(:tags).select('projects.*, count(tags.id) as tag_count')
    .where(tags: { id: tag_ids }).group('projects.id')
    .having('tag_count = ?', tag_ids.size)
end

这篇关于如何查找其has_many通过对象包括某个列表的所有对象的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！