Association Abuse?

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

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

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

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

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…

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.

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

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