...">
All geek questions in one place

Rails, Scope, OR and Combines

I have a scope:

includes(:countries).where("profiles.sector = :sector OR advices.sector = :sector", :sector => sector)

It creates 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 a country in my Profileand in my model Country)

Unfortunately, ORit seems to fail:

it does not display a profile that has only the corresponding sector, but does not have any advice. Thoughts?

+3
source share
3 answers

You do an INNER JOIN, so it requires profiles to have the appropriate advice. Instead, try the following:

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 without tips.

+7
source

You can do external joins by specifying a where clause with a hash after includes:

Post.includes(:comments).where(:comments=>{:user_id=>nil})

gives:

  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)

.

, , Rails SQL . , .

+7

Check out http://metautonomo.us/projects/metawhere/ for the greater kindness of the request ...

meta_where is no longer supported: https://github.com/activerecord-hackery/meta_where

Rails 5 introduces OR statements: Rails 5: ActiveRecord OR query

+3
source

Source: https://habr.com/ru/post/1793152/

More articles:


All Articles