With rails, how do I find objects NOT in a Has Many Through collection?

Question

Let's say an article has many tags through taggings.

Article
   has_many :taggings
   has_many :tags, :though :taggings
end

@article.tags #gives all the tags for that article

How do I find all the tags that this article does NOT have?

Thanks

Answer 1

The only way I can think of to do this using Rails finders would be to do two queries and subtract:

class Article
  def unused_tags
    Tag.all - self.tags
  end
end

Alternately, you could do this through SQL (which would be more efficient since you'd be only getting rows that you want):

query = <<-eos
SELECT *
FROM tags AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM taggings
  WHERE article_id = ?
    AND tag_id = t.id
)
eos
Tag.find_by_sql [query, article.id]

Answer 2

Here's what I came up with:

 class Article
    def missing_tags
       Tag.find(:all, :conditions => ['id NOT IN (SELECT taggings.tag_id FROM taggings WHERE (taggings.article_id = ?))', self.id])
    end
 end

@article.missing_tags