Rails, scope, OR and joins
Solution 1
You are doing an INNER JOIN, so it requires that the profiles have a corresponding advice. Try the following instead:
Profile
.joins("LEFT JOIN advices ON advices.profile_id = profiles.id")
.where("profiles.sector = :sector OR advices.sector = :sector", :sector => sector)
This will also include profiles that have no advices.
Solution 2
You can do outer joins by specifying a where clause with a hash after the includes
:
Post.includes(:comments).where(:comments=>{:user_id=>nil})
produces:
Post Load (0.5ms) SELECT "posts"."id" AS t0_r0, "posts"."created_at" AS t0_r1,
"posts"."updated_at" AS t0_r2, "comments"."id" AS t1_r0, "comments"."user_id"
AS t1_r1, "comments"."post_id" AS t1_r2, "comments"."content" AS t1_r3,
"comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5
FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
WHERE ("comments"."user_id" IS NULL)
Ryan Bigg wrote a helpful blog post about this.
EDIT
Be aware that this technique is more or less a side effect of the way Rails constructs the SQL for eager-loading associations. An explicit LEFT JOIN is more robust, as suggested in the accepted answer.
Solution 3
Check out http://metautonomo.us/projects/metawhere/ for more query goodness...
meta_where is now unmaintained: https://github.com/activerecord-hackery/meta_where
Rails 5 is introducing OR statements: Rails 5: ActiveRecord OR query
apneadiving
Freelancer, Ruby / Javascript engineer. soon author of Ruby book, "The Unhappy Path" Careers Hire me for a short and private session
Updated on June 17, 2022Comments
-
apneadiving almost 2 years
I have a scope:
includes(:countries).where("profiles.sector = :sector OR advices.sector = :sector", :sector => sector)
It produces the following SQL:
SELECT `profiles`.* FROM `profiles` INNER JOIN `advices` ON `advices`.`profile_id` = `profiles`.`id` WHERE (profiles.sector = 'Forestry_paper' OR advices.sector = 'Forestry_paper')
(yes I have country in my
Profile
and in myCountry
model)Unfortunately, the
OR
seems to fail:it doesn't render a profile having only the proper sector but no related advice. Thoughts?