This project is archived and is in readonly mode.

#1354 ✓wontfix
Paweł Kondzior

HABTM, :finder_sql and preload of associations bug

Reported by Paweł Kondzior | November 11th, 2008 @ 04:16 AM | in 2.x

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 = #{}},
                      :insert_sql =>%q{INSERT INTO "clients_pools" ("client_id", "pool_id") VALUES (#{self.clients_pools_client_id}, #{})}

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

if self.kind_id != 1

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.

Comments and changes to this ticket

  • Nicholas Thomas

    Nicholas Thomas November 28th, 2008 @ 05:22 PM

    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?

  • Paweł Kondzior

    Paweł Kondzior November 28th, 2008 @ 10:47 PM

    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.

  • Frederick Cheung

    Frederick Cheung December 20th, 2008 @ 01:35 PM

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

  • Anil Wadghule

    Anil Wadghule October 12th, 2010 @ 09:10 AM

    • Tag cleared.

    Automatic cleanup of spam.

  • Aditya Sanghi

    Aditya Sanghi October 12th, 2010 @ 10:14 AM

    Automatic cleanup of spam.

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=""></a>

People watching this ticket