Chaining Rails 3 scopes in has_many through association

Question

Is this doable?

I have the following scope:

class Thing < ActiveRecord::Base

scope :with_tag, lambda{ |tag| joins(:tags).where('tags.name = ?', tag.name)
                                           .group('things.id') }

def withtag_search(tags)
  tags.inject(scoped) do |tagged_things, tag|
    tagged_things.with_tag(tag) 
  end
end

I get a result if there's a single tag in the array of tags passed in with Thing.withtag_search(array_of_tags) but if I pass multiple tags in that array I get an empty relation as the result. In case it helps:

Thing.withtag_search(["test_tag_1", "test_tag_2"])

SELECT "things".* 
FROM "things" 
INNER JOIN "things_tags" ON "things_tags"."thing_id" = "things"."id" 
INNER JOIN "tags" ON "tags"."id" = "things_tags"."tag_id" 
WHERE (tags.name = 'test_tag_1') AND (tags.name = 'test_tag_2') 
GROUP BY things.id

=> [] # class is ActiveRecord::Relation

whereas

Thing.withtag_search(["test_tag_1"])

SELECT "things".* 
FROM "things" 
INNER JOIN "things_tags" ON "things_tags"."thing_id" = "things"."id" 
INNER JOIN "tags" ON "tags"."id" = "things_tags"."tag_id" 
WHERE (tags.name = 'test_tag_1') 
GROUP BY things.id

=> [<Thing id:1, ... >, <Thing id:2, ... >] # Relation including correctly all 
                                            # Things with that tag

I want to be able to chain these relations together so that (among other reasons) I can use the Kaminari gem for pagination which only works on relations not arrays - so I need a scope to be returned.

Answer 1

I also ran into this problem. The problem is not Rails, the problems is definitely MySQL:

Your SQL will create following temporary JOIN-table (only neccesary fields are shown):

+-----------+-------------+---------+------------+
| things.id | things.name | tags.id | tags.name  |
+-----------+-------------+---------+------------+
|     1     |     ...     |    1    | test_tag_1 |
+-----------+-------------+---------+------------+
|    1      |     ...     |    2    | test_tag_2 |
+-----------+-------------+---------+------------+

So instead joining all Tags to one specific Thing, it generates one row for each Tag-Thing combination (If you don't believe, just run COUNT(*) on this SQL statement). The problem is that you query criteria looks like this: WHERE (tags.name = 'test_tag_1') AND (tags.name = 'test_tag_2') which will be checked against each of this rows, and never will be true. It's not possible for tags.name to equal both test_tag_1 and test_tag_2 at the same time!

The standard SQL solution is to use the SQL statement INTERSECT... but unfortunately not with MySQL.

The best solution is to run Thing.withtag_search for each of your tags, collect the returning objects, and select only objects which are included in each of the results, like so:

%w[test_tag_1 test_tag_2].collect do |tag|
  Thing.withtag_search(tag)
end.inject(&:&)

If you want to get this as an ActiveRecord relation you can probably do this like so:

ids = %w[test_tag_1 test_tag_2].collect do |tag|
  Thing.withtag_search(tag).collect(&:id)
end.inject(&:&)
Things.where(:id => ids)

The other solution (which I'm using) is to cache the tags in the Thing table, and do MySQL boolean search on it. I will give you more details on this solution if you want.

Anyways I hope this will help you. :)