This project is archived and is in readonly mode.

#6608 open
eli.b

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

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

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.

Comments and changes to this ticket

  • Andrew White

    Andrew White March 23rd, 2011 @ 06:09 PM

    • 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.

  • eli.b

    eli.b March 23rd, 2011 @ 06:17 PM

    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

  • Andrew White

    Andrew White March 24th, 2011 @ 11:59 AM

    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"
    

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Pages