problem: activerecord (rails3), chaining scopes with includes

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

It turns out this is a bug; being patched for next Rails3 release. — insane.dreamer
– insane.dreamer, Commented Aug 10, 2010 at 2:55
yup, right now it takes includes only 1 time so if you want to circumvent the problem, make a new scope that includes both... includes(:country, :participant)... oops ... just realized thats buggy too sigh ... sorry — Ingo
– Ingo, Commented Mar 23, 2011 at 16:06

Zubin · Accepted Answer · 2011-04-05 08:02:52Z

5

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)

answered Apr 5, 2011 at 8:02

Zubin

9,8327 gold badges52 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

problem: activerecord (rails3), chaining scopes with includes

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related