Question

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

Answer 1

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)

友情链接