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.
