problem: activerecord (rails3), chaining scopes wi

In Rails3 there seems to be a problem when chaining two scopes (ActiveRelations) that each have a different include:

Consider these two scopes, both of which work fine on their own:

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 legibility)

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

Now the second scope:

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 legibility)

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

So both of those work properly individually.

Now, chain them together:

Work.global_only(user).not_belonging_to(user)

The 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 join from the second scope is ignored altogether. The SQL therefore fails on 'no such column 'participants.user_id'. If I chain the scopes in the reverse order, then the 'participants' join will be present and the 'countries' join will be lost. It's always the second join that is lost, it seems.

Does this look like a bug with ActiveRecord, or am I doing something wrong, or is this a "feature" :-)

(PS. Yes, I know, I can create a scope that joins both tables and it will correctly yield the result I want. I have that already. But I was trying to make smaller scopes than can be chained together in different ways, which is supposed to be the advantage of activerecord over straight sql.)

As a general rule, use :includes for eager-loading and :joins for conditions. In the second scope, the join SQL must be manually written because a left join is required.

That said, try this:

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)