Conditions on :include associations outer joins

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.

http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/5199cea1d6267c5c/06a1f5da4203a11d
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/7f7f715d917b9faa/426c768dd282929f


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

On Aug 18, 4:20 pm, Mark Reginald J. [email protected] wrote:

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?

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.