Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

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

#6608

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.

Reported by eli.b · March 23rd, 2011 @ 03:39 AM

State: open
Milestone: none
Assigned to: Andrew White Andrew White
Importance: Low

Activity

  1. Andrew White
    Andrew White
    • State changed from new to open
    • Assigned user set to Andrew White
    • Importance changed from to Low

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

    March 23rd, 2011 @ 06:09 PM

  2. eli.b
    eli.b

    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

    March 23rd, 2011 @ 06:17 PM

  3. Andrew White
    Andrew White

    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"

    March 24th, 2011 @ 11:59 AM