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.

HABTM, :finder_sql and preload of associations bug

#1354

Let's say we have this model: class Client < ActiveRecord::Base has_and_belongs_to_many :pools, :class_name => 'Pool', :foreign_key => 'client_id',

                      :finder_sql => %q{SELECT * FROM "pools" INNER JOIN "clients_pools" ON "pools".id = "clients_pools".pool_id WHERE ("clients_pools".client_id = #{self.clients_pools_client_id})},
                      :delete_sql => %q{DELETE FROM "clients_pools" WHERE client_id = #{self.clients_pools_client_id} AND pool_id = #{self.record.id}},
                      :insert_sql =>%q{INSERT INTO "clients_pools" ("client_id", "pool_id") VALUES (#{self.clients_pools_client_id}, #{record.id})}

protected # if clinet kind is other that base use parent_id def clients_pools_client_id

if self.kind_id != 1
  self.parent_id
else
  self.id
end

end end

In rails 2.2 when we will try to this: Client.first.pools

Sql will be generated from this method construct_sql: activerecord-2.2.0/lib/active_record/associations/has_and_belongs_to_many_association.rb

But when we will do preload Client.find(:first, :include => :pools).pools AR will try firstly to do preload, ommiting finder_sql and generating it from relection, execute it with [] result then it will again do construct_sql but now without any execution.

from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/associations/has_and_belongs_to_many_association.rb:80:in `construct_sql'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/associations/association_collection.rb:21:in `initialize'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/associations.rb:1289:in `new'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/associations.rb:1289:in `pools'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:180:in `send'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:180:in `preload_has_and_belongs_to_many_association'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:180:in `each'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:180:in `preload_has_and_belongs_to_many_association'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:120:in `send'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:120:in `preload_one_association'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:114:in `each'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:114:in `preload_one_association'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:91:in `preload_associations'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:90:in `preload_associations'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:90:in `each'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/association_preload.rb:90:in `preload_associations'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/base.rb:1492:in `find_every'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/base.rb:1452:in `find_initial'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.0/lib/active_record/base.rb:587:in `find'

It seems that something here is really bad. If we have :finder_sql preload shouldn't generate it own sql. It should just do :finders_sql.

I thnik preload_has_and_belongs_to_many_association should ignore preload of :pools and just execute :finder_sql query and return it as array. Just like we have it in has_many.

Reported by Paweł Kondzior · November 11th, 2008 @ 04:16 AM

State: wontfix
Milestone: 2.x
Assigned to: Jeremy Kemper Jeremy Kemper
Importance: none

Activity

  1. Nicholas Thomas
    Nicholas Thomas

    This is biting me also in has_many - it's really rather inconvenient :p.

    class Box has_many :connections, :dependent => :destroy, :finder_sql => 'select * from connections where box1_id = #{id} or box2_id = #{id}' end

    class Connections belongs_to :box1, :class_name => "Box", :foreign_key => :box1_id belongs_to :box2, :class_name => "Box", :foreign_key => :box2_id end

    I'm happy to help fix it if at all possible. In the meantime, is there a sensible workaround?

    November 28th, 2008 @ 05:22 PM

  2. Paweł Kondzior
    Paweł Kondzior

    I thnik adding :preload_sql for has_many, has_one and has_and_belongs_to_many that will bypass preload_has_and_belongs_to_many_association method would be solution here. I don't have right now time to check that this is even possible.

    November 28th, 2008 @ 10:47 PM

  3. Frederick Cheung
    Frederick Cheung
    • State changed from new to wontfix

    As it currently stands if you have finder_sql you're on your own for eager loading (both with preload and the old join based stuff) as the preloading code is just not going to be able to take your arbitrary sql statement and generalize it to produce a query return a set of objects.

    Adding a separate :preload_sql option would be possible, but i think it would have to be more than an sql fragment. A proc which given an array of (in this case) client objects returned an appropriate sql fragment. Until then I think this is a wontfix.

    December 20th, 2008 @ 01:35 PM

  4. Anil Wadghule
    Anil Wadghule
    • Tag cleared.

    Automatic cleanup of spam.

    October 12th, 2010 @ 09:10 AM

  5. Aditya Sanghi
    Aditya Sanghi

    Automatic cleanup of spam.

    October 12th, 2010 @ 10:14 AM