find_by_sql not settings model attributes and eager loading

rubyonrails-talk
1

Hi all,

I want to get all users for an event and the registration data: I use find_by_sql to get data for 2 models: users and registrations (user has many events through registrations).

@players = User.find_by_sql(["SELECT users.*, registrations.* FROM users LEFT JOIN registrations ON users.id = registrations.user_id AND registrations.event_id = ?", @event.id.to_s])

It is intended that when no registration is available I still get the user information. When no registration record is available though the the user.id attribute is nil in the user objects. This only happenes when no record for that user and event is available.

Someone knows why?

Also, can I somehow tell rails to fill the registrations data in the registrations object (like the eager loading :include in find) under the user object? Now, it all gets filled in the user object (registration.present for example is in the user model!)

Thanks Stijn

2

Hi all,

I want to get all users for an event and the registration data: I use find_by_sql to get data for 2 models: users and registrations (user has many events through registrations).

@players = User.find_by_sql(["SELECT users.*, registrations.* FROM users LEFT JOIN registrations ON users.id = registrations.user_id AND registrations.event_id = ?", @event.id.to_s])

It is intended that when no registration is available I still get the user information. When no registration record is available though the the user.id attribute is nil in the user objects. This only happenes when no record for that user and event is available.

Someone knows why?

Because you need to alias your columns. The result set will have a users.id column (not null) and a
registrations.id column (which is null). AR is just looking at the
column name and so sod's law means that the null id clobbers the not
null id (and in other cases players.id could easily be the
registration.id not the users.id)

Fred

Also, can I somehow tell rails to fill the registrations data in the registrations object (like the eager loading :include in find) under the user object? Now, it all gets filled in the user object (registration.present for example is in the user model!)

find_by_sql doesn't know how to do that. There is a plugin that allows
you to write custom sql with eager loading (http://kellogg-assoc.com/articles/2006/11/05/eager-finder-sql )