# More Complex Join with Rails/ActiveRecord

**URL:** <https://www.ruby-forum.com/t/more-complex-join-with-rails-activerecord/164969>\
**Category:** Rails\
**Created:** [April 7, 2009, 9:53pm UTC](https://www.ruby-forum.com/t/more-complex-join-with-rails-activerecord/164969 "2009-04-07T21:53:58Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jazcyk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e56c9b/32.png) [@jazcyk](https://www.ruby-forum.com/u/jazcyk)\
**Post date:** [April 7, 2009, 9:53pm UTC](https://www.ruby-forum.com/t/more-complex-join-with-rails-activerecord/164969/1 "2009-04-07T21:53:58Z")

</div>

Hi,

I have an Articles model.  
And a Moderations model.  
And a Users model.

Articles have many Users (moderators) through Moderations.  
Users have many Articles (moderated articles) through Moderations.

This works fine so far. I can link up a user with all their moderated  
articles etc.

But, I’d like a find which would select all the Articles, except it  
hides (does not return) the articles the current user has moderated. I  
am currently doing this in Ruby in two stages like this:

@articles\_list = Article.find(:all, :conditions =\> [“start\_date \>= ?”,  
today], :order =\> ‘start\_date’)  
@articles\_list -= current\_user.moderated\_articles if current\_user

And then displaying the articles list.

This works, but I fully suspect it is cripplingly slow, and I’d also  
like to paginate these articles and all the pagination tutorials seem to  
warn against fetching everything in one go. This seems like good advice.

So I am seeking guidance on a join condition that would do what I want.  
I’m guessing I need an inner join on the article\_id on the moderations  
table, but need to make sure that the user\_id of those records are the  
same as the current user only. So this is confusing me a bit.

Many thanks for any help you may offer!

- N

---

<div class="post-metadata">

**Author:** ![jazcyk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e56c9b/32.png) [@jazcyk](https://www.ruby-forum.com/u/jazcyk)\
**Post date:** [April 7, 2009, 10:10pm UTC](https://www.ruby-forum.com/t/more-complex-join-with-rails-activerecord/164969/2 "2009-04-07T22:10:41Z")

</div>

Just a quick try (you haven’t showed up your database schema):

@articles = Article.all( :conditions =\> [“start\_date \>= ? AND id NOT  
IN ( SELECT m.article\_id from moderations m WHERE m.user\_id = ? )”,  
today, current\_user.id], :order =\> ‘start\_date’)

- 

Maurício Linhares  
[http://alinhavado.wordpress.com/](http://alinhavado.wordpress.com/) (pt-br) | [http://blog.codevader.com/](http://blog.codevader.com/)  
(en)

On Tue, Apr 7, 2009 at 4:53 PM, Peter L.

---

<div class="post-metadata">

**Author:** ![jazcyk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e56c9b/32.png) [@jazcyk](https://www.ruby-forum.com/u/jazcyk)\
**Post date:** [April 7, 2009, 11:43pm UTC](https://www.ruby-forum.com/t/more-complex-join-with-rails-activerecord/164969/3 "2009-04-07T23:43:38Z")

</div>

This is awesome - exactly what I was after and I learned some SQL as  
well (which is clearly my weakness here).

Thanks for taking the time to type that up, you’ve really helped me out.

- N