Generated sql for has_many :through relation wrong when used with STI #949

Closed
lighthouse-import opened this Issue May 16, 2011 · 3 comments

1 participant

@lighthouse-import

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6608
Created by eli.b - 2011-03-23 03:39:09 UTC

Consider these models:


class First < ActiveRecord::Base

has_many :tags

has_many :thirds, :through => :tags

end

class Second < ActiveRecord::Base
end

class Third < Second

has_many :tags

has_many :firsts, :through => :tags

end

class Tag < ActiveRecord::Base

belongs_to :first

belongs_to :third

end

In other words, we have a has_many :through 'tag-style' relationship, but with one of the models (Third) being an STI inheriting from another (Second).
Say I wanted to do a join to see all instances of Third for some value of First:

@thirds = Third.joins(:firsts).where("first.id = 2")

This will work as expected; the generated sql (via to_sql) being:

SELECT seconds.* FROM seconds
INNER JOIN tags ON seconds.id = tags.third_id
INNER JOIN firsts ON firsts.id = tags.first_id
WHERE seconds.type = 'Third' AND (first.id = 1)

This doesn't work in the other direction:

@firsts = First.joins(:thirds).where("second.id = 2")

The SQL generated being:

SELECT firsts.* FROM firsts
INNER JOIN tags ON firsts.id = tags.first_id
INNER JOIN seconds ON seconds.type = 'Third'
WHERE (second.id = 2)

This results in tag duplication due to the fact that :seconds are not joined correctly with the tag table as in the first case above (see third line of sql statement in each case). All firsts with tags will show up in the resulting table, the WHERE clause being entirely ineffectual.

If something different needs to be stated in the ActiveRecord::Query statements above, it is not documented...

This is all using Rails 3.0.5.

@lighthouse-import

Imported from Lighthouse.
Comment by Andrew White - 2011-03-23 18:09:04 UTC

Yep, I've confirmed this - if you define the association in the base class it works as expected though.

@lighthouse-import

Imported from Lighthouse.
Comment by eli.b - 2011-03-23 18:17:24 UTC

Hey-
Thanks Andrew!
'if you define the association in the base class it works as expected though.'
What do you mean exactly? If I just put the class definition of Third inside second.rb? Sorry, I'm a little new to the not-so-simple associations game.
-e

@lighthouse-import

Imported from Lighthouse.
Comment by Andrew White - 2011-03-24 11:59:33 UTC

Using your models it would be like this:

class First < ActiveRecord::Base
  has_many :tags
  has_many :seconds, :through => :tags
end

class Second < ActiveRecord::Base
  has_many :tags
  has_many :firsts, :through => :tags
end

class Third < Second; end

class Tag < ActiveRecord::Base
  belongs_to :first
  belongs_to :seconds
end

Setting it up this way will return the appropriate models when use First.joins(:seconds).where(:seconds => { :id => 1 }). Using more descriptive model names will make it clearer:

class Vehicle < ActiveRecord::Base
  has_many :taggings
  has_many :tags, :through => :taggings
end
class Car < Vehicle; end
class Van < Vehicle; end
class Motorbike < Vehicle; end

class Tag < ActiveRecord::Base
  has_many :taggings
  has_many :vehicles, :through => :taggings
end

class Tagging < ActiveRecord::Base
  belongs_to :vehicle
  belongs_to :tag
end

>> Tag.joins(:vehicles).where("vehicles.id = 1").to_sql
=> "SELECT tags.* FROM tags INNER JOIN taggings ON tags.id = taggings.tag_id INNER JOIN vehicles ON taggings.vehicle_id = vehicles.id WHERE vehicles.id = 1"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment