Rails3 - 多对多关系和范围链

Question

假设我在文章和标签之间有多对多关系

class ArticleTag < ActiveRecord::Base
  belongs_to :article
  belongs_to :tag
end

class Tag < ActiveRecord::Base
  has_many :article_tags
  has_many :articles, :through => :article_tags
end

class Article < ActiveRecord::Base
  has_many :article_tags
  has_many :tags, :through => :article_tags

  named_scope :tagged, lambda { |id| joins(:tags).where("tags.id = ?", id) }
end

文章具有标记的范围，正如名称所示，它允许我检索标有特定标签的文章 令

我烦恼的是以下内容：

$ a = Article.create
 => #<Article id: 3, created_at: "2011-05-22 13:54:02", updated_at: "2011-05-22 13:54:02"> 
$ t1 = Tag.create
 => #<Tag id: 4, created_at: "2011-05-22 13:54:07", updated_at: "2011-05-22 13:54:07"> 
$ t2 = Tag.create
 => #<Tag id: 5, created_at: "2011-05-22 13:54:11", updated_at: "2011-05-22 13:54:11"> 
$ a.tags << t1
 => [#<Tag id: 4, created_at: "2011-05-22 13:54:07", updated_at: "2011-05-22 13:54:07">] 
$ a.tags << t2
 => [#<Tag id: 4, created_at: "2011-05-22 13:54:07", updated_at: "2011-05-22 13:54:07">, #<Tag id: 5, created_at: "2011-05-22 13:54:11", updated_at: "2011-05-22 13:54:11">] 
$ Article.tagged(t1.id)
 => [#<Article id: 3, created_at: "2011-05-22 13:54:02", updated_at: "2011-05-22 13:54:02">] 
$ Article.tagged(t2.id)
 => [#<Article id: 3, created_at: "2011-05-22 13:54:02", updated_at: "2011-05-22 13:54:02">] 
$ Article.tagged(t1.id).tagged(t2.id)
 => [] 

如果一篇文章被标记为两个标签，链接相应的范围不允许它的检索。这是应该的行为吗？如果是，我应该如何更改我的代码，以便最后一行不返回空数组？

PS：这里是生成的SQL。

SELECT \"articles\".* FROM \"articles\" INNER JOIN \"article_tags\" ON \"articles\".\"id\" = \"article_tags\".\"article_id\" INNER JOIN \"tags\" ON \"tags\".\"id\" = \"article_tags\".\"tag_id\" WHERE (tags.id = 4) AND (tags.id = 5)

Answer 1

虽然我不知道为什么内置链接失败，但这里有一个解决方案：

在您的 Article 模型

def self.tagged_by_one_in(*ids)
  return [] if ids.nil?
  self.joins(:tags).where("tags.id in (?)", ids).group(:article_id)
end

def self.tagged_by_all_in(*ids)
  return [] if ids.nil?
  #sorry raw sql there, found no Rails way.
  self.find_by_sql("select * from articles where id in (select article_id from article_tags where tag_id in (" + ids * ", " + ") group by article_id having count(*) = " + ids.size.to_s + ")" )
end

控制台中，您可以调用这些方法：

Article.tagged_by_one_in(1,2)
Article.tagged_by_all_in(1,2)

Answer 2

尝试这个

Article.rb 模型中

named_scope :tagged, lambda { |ids| joins(:tags).where("tags.id in (?)", ids) }

在控制台的

$ Article.tagged([t1.id,t2.id])

Answer 3

好的，我找到了解决方案。诀窍是手动创建连接，并在某些地方添加唯一的 id 以避免名称冲突。

named_scope :tagged, lambda { |id| uid = rand(36**8).to_s(36); joins("INNER JOIN article_tags AS at_#{uid} ON (articles.id = at_#{uid}.article_id) INNER JOIN tags AS tags_#{uid} ON (tags_#{uid}.id = at_#{uid}.tag_id)").where("tags_#{uid}.id = ?",id) }