Exclusive filtering by tag

Question

I'm using rails 3.0 and MySql 5.1

I have these three models:

Question, Tag and QuestionTag.

Tag has a column called name.

Question has many Tags through QuestionTags and vice versa.

Suppose I have n tag names. How do I find only the questions that have all n tags, identified by tag name.

And how do I do it in a single query.

(If you can convince me that doing it in more than one query is optimal, I'll be open to that)

A pure rails 3 solution would be preferred, but I am not adverse to a pure SQL solution either.

Please notice that the difficulty is in making a query which does not give all the questions that have any of the tags, but only the questions that have all the tags.

Answer 1

This is the solution I found for myself. Unmodified, it will only work in Rails 3 (or higher).

In the Tag model:

scope :find_by_names, lambda { |names| 
  unless names.empty?
    where("tags.name IN (#{Array.new(names.length, "?").join(",")})", *names) 
  else
    where("false")
  end
}  

In the Question model:

scope :tagged_with, lambda { |tag_names| 
  unless tag_names.blank?
    joins(:question_tags).
    where("questions.id = question_tags.question_id").
    joins(:tags).where("tags.id = question_tags.tag_id").
    group("questions.id").
    having("count(questions.id) = ?", tag_names.count) & Tag.find_by_names(tag_names) 
  else
    scoped
  end
}

The & Tag.find_by_names(tag_names) combines the two scopes such that the join on tags is really a join on the scoped model.

[Update]

My sql-fu has improved a little, so I thought I'd offer a pure SQL solution also:

SELECT q.*
FROM (
SELECT DISTINCT q.*
FROM `questions` q
JOIN question_tags qt
ON qt.question_id = q.id
JOIN tags t
ON t.id = qt.tag_id
WHERE t.name = 'dogs'
) AS q
JOIN question_tags qt
ON qt.question_id = q.id
JOIN tags t
ON t.id = qt.tag_id
WHERE t.name = 'cats'

This finds all the questions that have been tagged with both 'cats' and 'dogs'. The idea is to have a nested subquery for each tag I want to filter by.

There are several other ways to this. I'm not sure if it makes a difference to have the subquery in the FROM clause instead of the WHERE clause. Any insight would be appreciated.