Association Abuse?

Rails
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

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

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

On Aug 20, 9:19 am, Frederick C. [email protected]

4

On Wed, Aug 20, 2008 at 2:12 PM, codeturkey [email protected]
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

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…

From: [email protected]
[mailto:[email protected]] On Behalf Of Tim G.
Sent: Wednesday, August 20, 2008 11:51 AM
To: [email protected]
Subject: [Rails] Re: Association Abuse?

On Wed, Aug 20, 2008 at 2:12 PM, codeturkey
<[email protected]mailto:[email protected]> 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.idhttp://users.id = users_roles.user_id AND
users_roles.role_id = #{id})

–
Tim

6

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

7

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.

8

On Aug 20, 2008, at 4:43 PM, codeturkey wrote:

Additional AND clause on LOJ:
In this case, I think you have to do a sub select.

users_roles.user_id AND users_roles.role_id = #{id})

–
Tim

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 B. http://agileconsultingllc.com
[email protected]