Rails3 - many_to_many relationships and scope chaining

Let's say I have a many_to_many relation ship between Articles and Tags

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

Article has the scope tagged, which - as the name says - allows me to retrieve the Articles t开发者_JAVA技巧agged with a particular tag

What troubles me is the following :

$ 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)
 => [] 

If an article is tagged with two tags, chaining the corresponding scopes doesn't allow it's retrieval. Is it the supposed behavior? If it is, how should I change my code so that this last line doesn't return an empty array?

PS : here is the generated 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)

Although I don't know why the built-in chaining fails, here is a solution:

In your Article model

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

In console, you can call these methods:

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

Try this

In Article.rb model

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

In console

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

Ok, I found a solution. The trick is to manually create the joins, and add an unique id at some places to avoid name conflicts.

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) }