Hello,
Is there a way to handle includes twice?
Basic models, Group and User.
Group has many users, users belong to group.
Users have a ‘role’, which can be manager/developer and stuff like
that.
Say I want to find all groups that have a manager in them, including
all users.
Group.find(:all, :include => :users, :conditions => [“users.role = ?”,
‘Manager’]) won’t do the trick, since it will indeed find all groups
that have managers, but it fails to include all users.
Of course this is to be expected because of the conditions, but I
think there must be some way to include users twice in the query, once
for the conditions, and once as the ‘real output’
Ofcourse I can write it like this:
managed_groups_including_users =
User.find_all_by_role(‘Manager’, :include => {:group
=> :users}).map(&:group).uniq
But that involves 2 operations in memory (mapping and uniq’ing) which
isn’t nice to do on large datasets.
Also since it’s the groups I’m after, it doesn’t feel right to start
asking the User model.
Any thoughts?
Thanks,
Mathijs
[email protected] wrote:
Hello,
Is there a way to handle includes twice?
Basic models, Group and User.
Group has many users, users belong to group.
Users have a ‘role’, which can be manager/developer and stuff like
that.
Say I want to find all groups that have a manager in them, including
all users.
Group.find(:all, :include => :users, :conditions => [“users.role = ?”,
‘Manager’]) won’t do the trick, since it will indeed find all groups
that have managers, but it fails to include all users.
Of course this is to be expected because of the conditions, but I
think there must be some way to include users twice in the query, once
for the conditions, and once as the ‘real output’
Ofcourse I can write it like this:
managed_groups_including_users =
User.find_all_by_role(‘Manager’, :include => {:group
=> :users}).map(&:group).uniq
But that involves 2 operations in memory (mapping and uniq’ing) which
isn’t nice to do on large datasets.
Also since it’s the groups I’m after, it doesn’t feel right to start
asking the User model.
Any thoughts?
Thanks,
Mathijs
Group.find_by_sql <<-END select * from groups g
join users u on u.group_id = g.id
join users u2 on u2.group_id = u.group_id
where u2.role like ‘manager’
END
NOT TESTED and I am not on my machine so I can’t test it… but I think
something along these lines will avoid using the users twice… I give
no guarantees as to how this will scale as I think it may be a NxN
problem 
Anyways, let me know if your db blows up…
ilan
Hi Ilian,
Thanks for your help,
this is what happens:
gs=Group.find_by_sql “select * from groups g join users u on u.group_id = g.id join users u2 on u2.group_id = u.group_id where u2.role like ‘manager’”
=> [#<Group id: 2, name: “Richard”, created_at: “2008-02-25 21:09:39”,
updated_at: “2008-02-25 21:09:39”>, #<Group id: 2, name: “Richard”,
created_at: “2008-02-25 21:09:39”, updated_at: “2008-02-25 21:09:39”>]
gs[0]
=> #<Group id: 2, name: “Richard”, created_at: “2008-02-25 21:09:39”,
updated_at: “2008-02-25 21:09:39”>
Group.find(2)
=> #<Group id: 2, name: “Managers”, created_at: “2008-02-25 21:08:35”,
updated_at: “2008-02-25 21:08:35”>
I believe AR chokes on the “extra” attributes that get brought back.
Ofcourse this could be fixed by naming attributes explicitly, but
another this AR won’t do on find_by_sql (according to api docs) is
create associations with the extra data.
as seen by:
gs[0].users
which will fetch data again:
User Load (0.000486) SELECT * FROM users WHERE (users.group_id =
-
So I think using ray SQL in this case is not an option.