Problem: activerecord (rails3), include chain areas

Question

In Rails3, a problem arises when a chain of two areas (ActiveRelations), each of which is different, includes:

Consider these two areas, both of which work just fine:

First scope:

scope :global_only, lambda { |user| includes(:country) .where("countries.area_id <> ?", user.area) } 

Work.global_only (user) => (cut list of fields from SQL for readability)

 SELECT * FROM "works" LEFT OUTER JOIN "countries" ON "countries"."id" = "works"."country_id" WHERE (countries.area_id <> 3) 

Now the second area:

 scope :not_belonging_to, lambda { |user| includes(:participants) .where("participants.user_id <> ? or participants.user_id is null", user) } 

Work.not_belonging_to (user) => (cut list of fields from SQL for readability)

 SELECT * FROM "works" LEFT OUTER JOIN "participants" ON "participants"."work_id" = "works"."id" WHERE (participants.user_id <> 6 or participants.user_id is null) 

Thus, both of these functions work individually.

Now connect them together:

Work.global_only (user) .not_belonging_to (user)

SQL:

 SELECT (list of fields) FROM "works" LEFT OUTER JOIN "countries" ON "countries"."id" = "works"."country_id" WHERE (participants.user_id <> 6 or participants.user_id is null) AND (countries.area_id <> 3) 

As you can see, the connection to the second region is ignored altogether. Therefore, SQL fails on the "no such column" .user_id members. If I combine the regions in the reverse order, then there will be "participants" and the connection of the "countries" will be lost. It seems to be always the second connection that is lost.

This seems like an error with ActiveRecord, or I'm something wrong, or this is a “function” :-)

(PS. Yes, I know, I can create a region that combines both tables, and it will correctly produce the result that I want. I already have it. But I tried to make fewer regions than they can be combined in different ways, which are supposed to be an advantage of activerecord over direct sql.)

Answer 1

Typically, use :includes to load and :joins for conditions. In the second area, the SQL join must be written manually because a left join is required.

However, try the following:

 scope :global_only, lambda { |user| joins(:country). where(["countries.area_id != ?", user.area]) } scope :not_belonging_to, lambda { |user| joins("left join participants on participants = #{user.id}"). where("participants.id is null") } Work.global_only(user).not_belonging_to(user)