# Conditions on :include associations outer joins

**URL:** <https://www.ruby-forum.com/t/conditions-on-include-associations-outer-joins/112416>\
**Category:** Rails\
**Created:** [August 18, 2007, 4:38pm UTC](https://www.ruby-forum.com/t/conditions-on-include-associations-outer-joins/112416 "2007-08-18T16:38:58Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![stephen\_OSD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/df788c/32.png) [@stephen\_OSD](https://www.ruby-forum.com/u/stephen_OSD)\
**Post date:** [August 18, 2007, 4:38pm UTC](https://www.ruby-forum.com/t/conditions-on-include-associations-outer-joins/112416/1 "2007-08-18T16:38:58Z")

</div>

I have three models

User  
has\_many :reviews  
has\_many :review\_invites  
end

Review  
has\_one :user  
end

ReviewInvite  
has\_one\_user  
end

For someone to access a particular review, they need to own it, or be  
invited.

@user = User.find(user\_id  
:include =\> [:review, :review\_invite],  
:condition =\> 'filter to a certain review  
id);

Even if a user has no review invites or owns no reviews, I still want  
the user data returned, with all other fields padded null, ie standard  
outer join behavior.

Rails generates SQL like the following:

select columns  
from users  
left outer join reviews on reviews.user\_id = users.id  
left outer join reviewers on reviewers.user\_id = users.id  
where my\_conditions\_clause  
and user.id = user\_id;

In my mind, this SQL is not correct for what I want (it doesn’t work  
correctly either). It seems to join all the tables and then apply a  
filter to joined result, while I really want to filter the tables in  
the join conditions, like this:

select columns  
from users  
left outer join reviews on reviews.user\_id = users.id AND REVIEWS.ID  
= param[:review\_id]  
left outer join reviewers on reviewers.user\_id = users.id AND  
REVIEWERS.ID = param[:review\_id]  
where user.id = user\_id;

Which does give me the answer I want, even if a user has no reviews or  
invites.

I have fudged this behavior in before when using a single :include by  
specifying a joins parameter, but I cannot see how I can do this with  
two associations. Is there any way to get what I want without  
resorting to SQL?

It seems to me like the Rails way to do this is something like

@user = User.find(user\_id  
:include =\> [:review =\> { :conditions =\>  
[‘review.id = ?’, review\_id] },  
:review\_invite =\>  
{ conditions =\> [‘reviewers.review\_id’ = ?] } )

But I cannot find anything in the docs that implies this is possible.

Please help!

Thanks,

Stephen.

---

<div class="post-metadata">

**Author:** ![stephen\_OSD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/df788c/32.png) [@stephen\_OSD](https://www.ruby-forum.com/u/stephen_OSD)\
**Post date:** [August 18, 2007, 5:22pm UTC](https://www.ruby-forum.com/t/conditions-on-include-associations-outer-joins/112416/2 "2007-08-18T17:22:03Z")

</div>

[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/5199cea1d6267c5c/06a1f5da4203a11d](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/5199cea1d6267c5c/06a1f5da4203a11d)  
[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/7f7f715d917b9faa/426c768dd282929f](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/7f7f715d917b9faa/426c768dd282929f)

–  
We develop, watch us RoR, in numbers too big to ignore.

---

<div class="post-metadata">

**Author:** ![stephen\_OSD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/df788c/32.png) [@stephen\_OSD](https://www.ruby-forum.com/u/stephen_OSD)\
**Post date:** [August 18, 2007, 5:51pm UTC](https://www.ruby-forum.com/t/conditions-on-include-associations-outer-joins/112416/3 "2007-08-18T17:51:12Z")

</div>

On Aug 18, 4:20 pm, Mark Reginald J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> [http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/...http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/...http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/)…
> 
> –  
> We develop, watch us RoR, in numbers too big to ignore.

Thanks!

reflect\_on\_association does the trick. I take it that does something  
to the User class behind the scenes, so I best do something like

User.reflect\_on\_association(:reviewers).options[:conditions] = nil

After using it so I don’t end up with a nasty bug somewhere else?

---

<div class="post-metadata">

**Author:** ![stephen\_OSD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/df788c/32.png) [@stephen\_OSD](https://www.ruby-forum.com/u/stephen_OSD)\
**Post date:** [August 22, 2007, 4:48am UTC](https://www.ruby-forum.com/t/conditions-on-include-associations-outer-joins/112416/4 "2007-08-22T04:48:27Z")

</div>

stephen O’D wrote:

> reflect\_on\_association does the trick. I take it that does something  
> to the User class behind the scenes, so I best do something like
> 
> User.reflect\_on\_association(:reviewers).options[:conditions] = nil
> 
> After using it so I don’t end up with a nasty bug somewhere else?

Yes, either clear the conditions option after the find, or create  
a separate association that has its conditions manipulated, leaving  
the original unaltered.

–  
We develop, watch us RoR, in numbers too big to ignore.