I have the following relationships:
users -> user_roles <- roles
In my Role model, I created a habtm association with users:
I often find myself wanting to have a list of all the users that
aren''t associated with a given role so I thought that might be a good
candidate for a named association.
class Role
has_and_belongs_to_many :users, :join_table =>
''user_roles''
has_and_belongs_to_many :non_users, :class_name =>
''User'', :join_table => ''user_roles'',
:finder_sql => ''select * from
users where id NOT IN(select user_id from user_roles where role_id
#{id})''
end
This sort of works, except non_users is cached. So if I update the
users associated with a role, then I have to explicitly call
r.non_users(true). I suppose I could create a callback in my model
that reloads the non_users every time the users collection is
updated. Does this seem like a bad idea? I''m hoping to get some
feedback.
I''ve also played with creating a standard non_users method in the
model, however making an association seems cleaner:
class Role
has_and_belongs_to_many :users, :join_table =>
''user_roles''
def non_users
User.find_by_sql("select * from users where id NOT IN(select
user_id from user_roles where role_id = #{id})")
end
end
Any suggestions people can provide would be great.
Thanks,
Steven
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
On 20 Aug 2008, at 17:05, codeturkey wrote:> > > This sort of works, except non_users is cached. So if I update the > users associated with a role, then I have to explicitly call > r.non_users(true). I suppose I could create a callback in my model > that reloads the non_users every time the users collection is > updated. Does this seem like a bad idea? I''m hoping to get some > feedback. >If you''re on 2.1 this sounds like a good fit for a named_scope (and if you use a left outer join you can get rid of the subselect) Fred> I''ve also played with creating a standard non_users method in the > model, however making an association seems cleaner: > > class Role > has_and_belongs_to_many :users, :join_table => ''user_roles'' > > def non_users > User.find_by_sql("select * from users where id NOT IN(select > user_id from user_roles where role_id = #{id})") > end > end > > > Any suggestions people can provide would be great. > > > Thanks, > Steven > > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks Frederick. I am using rails 2.1, but I''m not sure if using named_scope is appropriate here since non_users does not have any meaning in the context of the Role class, only in an instance of the class. Role.non_users #=> makes no sense. As far as doing a left outer join, are you sure that will work? Initially I tried doing it that way but couldn''t get it to work. If it''s not too much trouble, how would I accomplish this with a LOJ? Best, Steven On Aug 20, 9:19 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 20 Aug 2008, at 17:05, codeturkey wrote: > > > > > This sort of works, except non_users is cached. So if I update the > > users associated with a role, then I have to explicitly call > > r.non_users(true). I suppose I could create a callback in my model > > that reloads the non_users every time the users collection is > > updated. Does this seem like a bad idea? I''m hoping to get some > > feedback. > > If you''re on 2.1 this sounds like a good fit for a named_scope (and > if you use a left outer join you can get rid of the subselect) > > Fred > > > I''ve also played with creating a standard non_users method in the > > model, however making an association seems cleaner: > > > class Role > > has_and_belongs_to_many :users, :join_table => ''user_roles'' > > > def non_users > > User.find_by_sql("select * from users where id NOT IN(select > > user_id from user_roles where role_id = #{id})") > > end > > end > > > Any suggestions people can provide would be great. > > > Thanks, > > Steven--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Wed, Aug 20, 2008 at 2:12 PM, codeturkey <codeturkey-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> If it''s not too much trouble, how would I accomplish this with a LOJ? >The LOJ would look like this: SELECT users.* FROM users LEFT OUTER JOIN users_roles ON (users.id users_roles.user_id AND users_roles.role_id = #{id}) -- Tim --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Don''t you want a
WHERE users_roles.user_id IS NULL
on there to filter out the users who have already been assigned that role?
I thought that''s what the OP wanted to do...
________________________________
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Tim Gossett
Sent: Wednesday, August 20, 2008 11:51 AM
To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
Subject: [Rails] Re: Association Abuse?
On Wed, Aug 20, 2008 at 2:12 PM, codeturkey
<codeturkey-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org<mailto:codeturkey-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>>
wrote:
If it''s not too much trouble, how would I accomplish this with a LOJ?
The LOJ would look like this:
SELECT users.*
FROM users LEFT OUTER JOIN users_roles
ON (users.id<http://users.id> = users_roles.user_id AND
users_roles.role_id = #{id})
--
Tim
--~--~---------~--~----~------------~-------~--~----~
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@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
This will not work. Let''s say I have the following data and I want to find all users that are not associated with role 2. users 1, u1 2, u2 3, u3 4, u4 roles 1, r1 2, r2 3, r3 users_roles 1, 3 2, 3 3, 3 1, 2 Basic LOJ: select * from users LEFT OUTER JOIN user.id ON users_roles.user_id This will return: 1 u1 1 3 1 u1 1 2 2 u2 2 3 3 u3 3 3 4 u4 Additional AND clause on LOJ: SELECT * FROM users LEFT OUTER JOIN user_roles ON (users.id user_roles.user_id AND user_roles.role_id != 2) This will return: 1 u1 1 3 2 u2 2 3 3 u3 3 3 4 u4 In this case, I think you have to do a sub select. On Aug 20, 11:50 am, "Tim Gossett" <t...-Ld8XL9z/NMTby3iVrkZq2A@public.gmane.org> wrote:> On Wed, Aug 20, 2008 at 2:12 PM, codeturkey <codetur...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > If it''s not too much trouble, how would I accomplish this with a LOJ? > > The LOJ would look like this: > > SELECT users.* FROM users LEFT OUTER JOIN users_roles ON (users.id > users_roles.user_id AND users_roles.role_id = #{id}) > > -- > Tim--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ok, you can do this query with a LOJ: select * from users left join user_roles on (users.id = user_roles.user_id and user_roles.role_id = 2 ) where user_roles.user_id is null --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Aug 20, 2008, at 4:43 PM, codeturkey wrote:> This will not work. > > Let''s say I have the following data and I want to find all users that > are not associated with role 2. > > users > 1, u1 > 2, u2 > 3, u3 > 4, u4 > > roles > 1, r1 > 2, r2 > 3, r3 > > users_roles > 1, 3 > 2, 3 > 3, 3 > 1, 2 > > Basic LOJ: > select * from users LEFT OUTER JOIN user.id ON users_roles.user_id > > This will return: > 1 u1 1 3 > 1 u1 1 2 > 2 u2 2 3 > 3 u3 3 3 > 4 u4 > > Additional AND clause on LOJ: > SELECT * FROM users LEFT OUTER JOIN user_roles ON (users.id > user_roles.user_id AND user_roles.role_id != 2) > > This will return: > 1 u1 1 3 > 2 u2 2 3 > 3 u3 3 3 > 4 u4 > > > In this case, I think you have to do a sub select. > > On Aug 20, 11:50 am, "Tim Gossett" <t...-Ld8XL9z/NMTby3iVrkZq2A@public.gmane.org> wrote: >> On Wed, Aug 20, 2008 at 2:12 PM, codeturkey <codetur...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >> wrote: >>> If it''s not too much trouble, how would I accomplish this with a >>> LOJ? >> >> The LOJ would look like this: >> >> SELECT users.* FROM users LEFT OUTER JOIN users_roles ON (users.id >> users_roles.user_id AND users_roles.role_id = #{id}) >> >> -- >> Timcreate table users (id INTEGER, name); insert into users (id, name) values (1, ''u1''); insert into users (id, name) values (2, ''u2''); insert into users (id, name) values (3, ''u3''); insert into users (id, name) values (4, ''u4''); create table roles (id INTEGER, name); insert into roles (id, name) values (1, ''r1''); insert into roles (id, name) values (2, ''r2''); insert into roles (id, name) values (3, ''r3''); create table users_roles (user_id INTEGER, role_id INTEGER); insert into users_roles (user_id, role_id) values (1, 3); insert into users_roles (user_id, role_id) values (2, 3); insert into users_roles (user_id, role_id) values (3, 3); insert into users_roles (user_id, role_id) values (1, 2); SELECT users.* FROM users LEFT OUTER JOIN (SELECT users_roles.user_id AS user_id, roles.name AS role_name FROM users_roles JOIN roles ON roles.id = users_roles.role_id WHERE roles.name = ''r2'') AS roles_tmp ON roles_tmp.user_id = users.id WHERE roles_tmp.role_name IS NULL; 2|u2 3|u3 4|u4 Same thing, but with ''r3'' gives: 4|u4 Or with ''r1'': 1|u1 2|u2 3|u3 4|u4 So you *can* do it with a LOJ, but the relation on the right is a subquery that returns users that have the role and the WHERE clause excludes those users by picking only the ones that *don''t* have a match. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---