How on earth is this rails query working?

Question

I have just optimised some Ruby code that was in a controller method, replacing it with a direct database query. The replacement appears to work and is much faster. Thing is, I've no idea how Rails managed to figure out the correct query to use!

The purpose of the query is to work out tag counts for Place models within a certain distance of a given latitude and longitude. The distance part is handled by the GeoKit plugin (which basically adds convenience methods to add the appropriate trigonometry calculations to the select), and the tagging part is done by the *acts_as_taggable_on_steroids* plugin, which uses a polymorphic association.

Below is the original code:

places = Place.find(:all, :origin=>latlng, :order=>'distance asc', :within=>distance, :limit=>200)
tag_counts = MyTag.tagcounts(places)
deep_tag_counts=Array.new()
tag_counts.each do |tag|
  count=Place.find_tagged_with(tag.name,:origin=>latlng, :order=>'distance asc', :within=>distance, :limit=>200).size
  deep_tag_counts<<{:name=>tag.name,:count=>count}
end

where the MyTag class implements this:

  def MyTag.tagcounts(places)
    alltags = places.collect {|p| p.tags}.flatten.sort_by(&:name)
    lasttag=nil;
    tagcount=0;
    result=Array.new
    alltags.each do |tag| 
      unless (lasttag==nil || lasttag.name==tag.name) 
        result << MyTag.new(lasttag,tagcount)
        tagcount=0
      end
      tagcount=tagcount+1
      lasttag=tag
    end
    unless lasttag==nil then 
      result << MyTag.new(lasttag,tagcount)
    end
    result
  end

This was my (very ugly) first attempt as I originally found it difficult to come up with the right rails incantations to get this done in SQL. The new replacement is this single line:

deep_tag_counts=Place.find(:all,:select=>'name,count(*) as count',:origin=>latlng,:within=>distance,:joins=>:tags, :group=>:tag_id)

Which results in an SQL query like this:

SELECT name,count(*) as count, (ACOS(least(1,COS(0.897378837271255)*COS(-0.0153398733287034)*COS(RADIANS(places.lat))*COS(RADIANS(places.lng))+
 COS(0.897378837271255)*SIN(-0.0153398733287034)*COS(RADIANS(places.lat))*SIN(RADIANS(places.lng))+
 SIN(0.897378837271255)*SIN(RADIANS(places.lat))))*3963.19)
 AS distance FROM `places` INNER JOIN `taggings` ON (`places`.`id` = `taggings`.`taggable_id` AND `taggings`.`taggable_type` = 'Place') INNER JOIN `tags` ON (`tags`.`id` = `taggings`.`tag_id`) WHERE (places.lat>50.693170735732 AND places.lat<52.1388692642679 AND places.lng>-2.03785525810908 AND places.lng<0.280035258109084 AND (ACOS(least(1,COS(0.897378837271255)*COS(-0.0153398733287034)*COS(RADIANS(places.lat))*COS(RADIANS(places.lng))+
 COS(0.897378837271255)*SIN(-0.0153398733287034)*COS(RADIANS(places.lat))*SIN(RADIANS(places.lng))+
 SIN(0.897378837271255)*SIN(RADIANS(places.lat))))*3963.19)
 <= 50) GROUP BY tag_id

Ignoring the trig (which is from GeoKit, and results from the :within and :origin parameters), what I can't figure out about this is how on earth Rails was able to figure out from the instruction to join 'tags', that it had to involve 'taggings' in the JOIN (which it does, as there is no direct way to join the places and tags tables), and also that it had to use the polymorphic stuff.

In other words, how the heck did it (correctly) come up with this bit:

INNER JOIN `taggings` ON (`places`.`id` = `taggings`.`taggable_id` AND `taggings`.`taggable_type` = 'Place') INNER JOIN `tags` ON (`tags`.`id` = `taggings`.`tag_id`)

...given that I never mentioned the taggings table in the code! Digging into the taggable plugin, the only clue that Rails has seems to be this:

class Tag < ActiveRecord::Base
  has_many :taggings, :dependent=>:destroy

...
end

Anybody able to give some insight into the magic going on under the hood here?

Answer 1

The *acts_as_taggable_on_steroids* plugin tells your Place model that it has_many Tags through Taggings. With this association specified, ActiveRecord knows that it needs to join taggings in order to get to the tags table. The same thing holds true for HABTM relationships. For example:

class Person < ActiveRecord::Base
  has_and_belongs_to_many :tags
end

class Tag < ActiveRecord::Base
  has_and_belongs_to_many :people
end

>> Person.first(:joins => :tags)

This produces the following SQL:

SELECT "people".* 
FROM "people" 
  INNER JOIN "people_tags" ON "people_tags".person_id = "people".id 
  INNER JOIN "tags" ON "tags".id = "people_tags".tag_id 
LIMIT 1