Rails - Aug 2007 - 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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Aug 18, 4:20 pm, Mark Reginald James
<m...-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
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?


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---