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.

named_scope join issue, nil ids

#2274

Here are three examples which I would expect to be the same. The first works best. The second is not functional, since the Address records come back with nil ids. The third works ok, but duplicates the condition in the WHERE clause. (Bonus points if you can get that condition to go on the ON clause of the INNER JOIN).

This is ActiveRecord 2.2


class Address < ActiveRecord::Base
  # doesn't quite work
  named_scope :not_invited, :joins => "LEFT JOIN invites ON addresses.id = invites.address_id",
                            :conditions => "invites.id IS NULL"
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :contacts, :class_name => 'Address'

  def contacts_not_invited
    contacts.all(:joins => "LEFT JOIN invites ON addresses.id = invites.address_id",
                 :conditions => "invites.id IS NULL")
  end
end

current_user = User.find_by_email("aaron@example.com")

p current_user.contacts_not_invited
# => Address Load (0.0ms)  SELECT "addresses".* FROM "addresses" INNER JOIN "addresses_users" ON "addresses".id = "addresses_users".address_id LEFT JOIN invites ON addresses.id = invites.address_id WHERE (invites.id IS NULL) AND ("addresses_users".user_id = 317248456 ) 
# => [#<Address id: 718047205, email: "woo@thanks.com">, #<Address id: 1068445024, email: "newguy@place.com">]

p current_user.contacts.not_invited
# => Address Load (0.0ms)  SELECT * FROM "addresses" LEFT JOIN invites ON addresses.id = invites.address_id INNER JOIN "addresses_users" ON "addresses".id = "addresses_users".address_id WHERE ("addresses_users".user_id = 317248456 ) AND ((invites.id IS NULL) AND ("addresses_users".user_id = 317248456 ))
# XXX: address records ids are nil!!
# => [#<Address id: nil, email: "woo@thanks.com">, #<Address id: nil, email: "newguy@place.com">]

p current_user.contacts.not_invited.all
# => Address Load (0.0ms)  SELECT "addresses".* FROM "addresses" LEFT JOIN invites ON addresses.id = invites.address_id INNER JOIN "addresses_users" ON "addresses".id = "addresses_users".address_id WHERE (((invites.id IS NULL) AND ("addresses_users".user_id = 317248456 )) AND ("addresses_users".user_id = 317248456 )) 
# => [#<Address id: 718047205, email: "woo@thanks.com">, #<Address id: 1068445024, email: "newguy@place.com">]

Reported by Greg Hazel · March 17th, 2009 @ 05:09 PM

State: invalid
Milestone: 2.x
Assigned to: Pratik Pratik
Importance: none

Activity

  1. Dmitry Polushkin
    Dmitry Polushkin
    • Tag set to ids, joins, wrong

    I've had similar problem (wrong ids), when used named_scope through HAMTB model.

    March 21st, 2009 @ 12:57 AM

  2. Frederick Cheung
    Frederick Cheung
    • State changed from new to invalid

    You need to specify :select => 'addresses.*' or else the id column from the joined table clobbers the one from the addresses table.

    May 10th, 2009 @ 03:59 PM

  3. Dmitry Polushkin
    Dmitry Polushkin
    • Assigned user set to Pratik

    @Frederick Cheung: Are you sure that it works correctly?

    May 11th, 2009 @ 02:10 PM

  4. Frederick Cheung
    Frederick Cheung

    been there, done that got the tshirt (there was also a thread on the core list a while back proposing that select => 'foo.*' be the default). find default :select to 'table.*' when you specify :joins, named_scope should quite possible do that too.

    May 11th, 2009 @ 02:39 PM