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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs