# Selecting data with two join tables

**URL:** <https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399>\
**Category:** Rails\
**Created:** [November 6, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399 "2007-11-06T18:54:18Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/1 "2007-11-06T18:54:18Z")

</div>

hy …

I have a problem with selecting data from my database.  
this is my database:

users that has\_and\_belongs\_to\_many rights  
articles that has\_and\_belongs\_to\_many rights  
rights that has\_and\_belongs\_to\_many users and articles.

If I have a user ID… How can I get the articles which he has the rights  
to ?

something like this? :

@article\_list = User.find\_by\_id(0).rights.articles

but this doesn’t work  
I hope someone has the answer

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 7:37pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/2 "2007-11-06T19:37:07Z")

</div>

You have to do it yourself in Ruby if you do it like that, or you will  
need  
to write some SQL.

class User \< ActiveRecord::Base

def articles  
@articles||= self.rights.collect{|right|  
right.articles.flatten}.flatten  
end

end

That will allow you to do

u = User.find(1)  
@articles = u.articles

Or you could write a more complex find\_by\_sql that would to that for you  
which may be more efficient.

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 8:05pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/3 "2007-11-06T20:05:18Z")

</div>

@William: Yeah. Good catch. I forgot to add that.

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 8:06pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/4 "2007-11-06T20:06:19Z")

</div>

THANXS !! (I was strugling with this problem for ages :P)

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 7:40pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/5 "2007-11-06T19:40:02Z")

</div>

I would add a .uniq! to the end as well since articles could belong to  
multiple rights and so could a user.

def articles  
@articles||= self.rights.collect{|right|  
right.articles.flatten}.flatten.uniq!  
end

-Bill

Brian H. wrote:

> end  
> you which may be more efficient.
> 
> ```
> rights that has_and_belongs_to_many users and articles.
> 
> but this doesn't work
> I hope someone has the answer
> --
> Posted via http://www.ruby-forum.com/.
> 
> ```
> 
> >

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 8:13pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/6 "2007-11-06T20:13:28Z")

</div>

I’m pretty sure you can drop the first flatten and also, to avoid the  
n+1 query problem, adding :include would almost certainly be more  
efficient:

def articles  
@articles||= self.rights.find(:all, :include =\>  
:articles).collect{|right| right.articles }.flatten.uniq!  
end

-Bill

William P. wrote:

> Brian H. wrote:
> 
> > end  
> > you which may be more efficient.
> > 
> > ```
> > rights that has_and_belongs_to_many users and articles.
> > 
> > ```
> 
> –  
> Sincerely,
> 
> William P.
> 
> >

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 6, 2007, 8:42pm UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/7 "2007-11-06T20:42:32Z")

</div>

THANXs for all the replays

but it doesn’t work…  
if I try to do it but I get:  
undefined method `rights’ for #ApplicationController:0x22a8eb8

I do this:  
in my application controller:  
@article\_list ||= self.rights.find(:all, :include =\>:articles)  
.collect{|right| right.articles }.flatten.uniq!

and then it will do: (of course in a layout and not in the controller  
:P)  
\<% for article in @article\_list %\>  
…  
\<% end %\>

does anyone now what the problem is here ?  
and yes my models work (I can without problems ask for al the articles  
with right is 1 etc.)

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 7, 2007, 1:34am UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/8 "2007-11-07T01:34:26Z")

</div>

In your User model, add this:

def articles  
@article\_list ||= self.rights.find(:all, :include  
=\>:articles).collect{|right| right.articles }.flatten.uniq!  
end

Then in your controller, call the articles method on the current user.  
Say you have @user in your controller, then this would work:

@user.articles

Note that the first method goes in the User model, not in your  
controller.

-Bill

Heldop S. wrote:

> with right is 1 etc.)

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![heldopslippers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b9bd4f/32.png) [@heldopslippers](https://www.ruby-forum.com/u/heldopslippers)\
**Post date:** [November 7, 2007, 9:56am UTC](https://www.ruby-forum.com/t/selecting-data-with-two-join-tables/119399/9 "2007-11-07T09:56:47Z")

</div>

Ok well I am an idiot … beceause I can’t read ☹ …  
(it was explained in the first post)

Sorry and thanxs guys