Rails, scope, OR and joins

Question

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 my Country model)

Unfortunately, the OR seems to fail:

it doesn't render a profile having only the proper sector but no related advice. Thoughts?

score 11 · Accepted Answer · answered Feb 18 '11 at 22:26

11

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.

answered Feb 18 '11 at 22:26

Pan Thomakos

34,082
9
88
85

thanks :) no better Rails way to write it? Moreover, how to chain the joins with LEFT JOIN? – apneadiving Feb 18 '11 at 22:30
it's ok, a mere AND makes it perfectly – apneadiving Feb 18 '11 at 22:35
There's no other Rails way to write the join. The joins method only works with inner join (http://guides.rubyonrails.org/active_record_querying.html#joining-tables) – Pan Thomakos Feb 18 '11 at 22:37
1

You can always add additional joins calls: joins(...).joins(...) and you can combine conditions with AND or OR: "LEFT JOIN advices ON ... AND ... AND ... – Pan Thomakos Feb 18 '11 at 22:37
>> There's no other Rails way to write the join << See my answer for a way to do this. – zetetic Feb 19 '11 at 00:27
Okay, I guess I should restate: it's possible to write left joins in another way in Rails, but it's not pretty. I definitely prefer the explicit "LEFT JOIN" since it's clearer what you are attempting to accomplish. Using includes is meant to eagerly-load associations, not differentiate between inner and outer joins. – Pan Thomakos Feb 19 '11 at 00:43

zetetic · Answer 2 · 2014-09-25T17:15:43.153

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.

score 4 · Answer 3 · edited May 23 '17 at 12:22

4

~~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

edited May 23 '17 at 12:22

Community

1
1

answered May 27 '11 at 13:23

DGM

26,629
7
58
79

1

Updated the answer a bit. – DGM Jul 15 '16 at 15:33

Rails, scope, OR and joins

3 Answers3