find_by_sql not settings model attributes and eager loading

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

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
)