# Association Abuse?

**URL:** <https://www.ruby-forum.com/t/association-abuse/146407>\
**Category:** Rails\
**Created:** [August 20, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/association-abuse/146407 "2008-08-20T18:15:31Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/1 "2008-08-20T18:15:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 6:20pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/2 "2008-08-20T18:20:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 8:13pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/3 "2008-08-20T20:13:19Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 8:51pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/4 "2008-08-20T20:51:25Z")

</div>

On Wed, Aug 20, 2008 at 2:12 PM, codeturkey [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 9:54pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/5 "2008-08-20T21:54:44Z")

</div>

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: removed\_email\_address@domain.invalid  
[[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)] On Behalf Of Tim G.  
Sent: Wednesday, August 20, 2008 11:51 AM  
To: removed\_email\_address@domain.invalid  
Subject: [Rails] Re: Association Abuse?

On Wed, Aug 20, 2008 at 2:12 PM, codeturkey  
\<removed\_email\_address@domain.invalid[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)\> 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](http://users.id) = users\_roles.user\_id AND  
users\_roles.role\_id = #{id})

–  
Tim

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 11:33pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/6 "2008-08-20T23:33:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 10:44pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/7 "2008-08-20T22:44:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![codeturkey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ac91a4/32.png) [@codeturkey](https://www.ruby-forum.com/u/codeturkey)\
**Post date:** [August 20, 2008, 11:36pm UTC](https://www.ruby-forum.com/t/association-abuse/146407/8 "2008-08-20T23:36:11Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid