Association Abuse?

rubyonrails-talk
1

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

2

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

3

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

4

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})

5

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…

6

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.

7

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

8

create 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@AgileConsultingLLC.com