Tarscher
2008-Feb-07 17:03 UTC
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Feb-07 17:16 UTC
Re: find_by_sql not settings model attributes and eager loading
On 7 Feb 2008, at 17:03, Tarscher wrote:> > 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 )> > > > Thanks > Stijn > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---