ruby-on-rails - 在 ActiveRecord 中包含 where 子句

Question

我想预加载关联，但如果不存在关联，我想返回主要关系。

要了解我的问题，请使用可重现的脚本:

# frozen_string_literal: true
gem 'rails'
require 'active_record'

puts "Active Record #{ActiveRecord::VERSION::STRING}"

ActiveRecord::Base.establish_connection(
  adapter: 'sqlite3',
  database: ':memory:'
)

ActiveRecord::Schema.define do
  create_table :organisations, force: true do |t|
    t.string 'name', limit: 255, null: false
    t.datetime 'created_at'
    t.datetime 'updated_at'
  end

  create_table :groups, force: true do |t|
    t.string 'name', limit: 255, null: false
    t.integer 'shop_id', null: false
    t.datetime 'created_at'
    t.datetime 'updated_at'
  end

  create_table :groups_organisations, force: true do |t|
    t.integer  'organisation_id', null: false
    t.integer  'group_id', null: false
    t.datetime 'created_at'
    t.datetime 'updated_at'
  end
end

class Organisation < ActiveRecord::Base
  has_many :groups_organisations
  has_many :groups, through: :groups_organisations
end

class Group < ActiveRecord::Base
  has_many :groups_organisations
  has_many :organisation, through: :groups_organisations
end

class GroupsOrganisation < ActiveRecord::Base
  belongs_to :group
  belongs_to :organisation
end

organisation_without_groups = Organisation.create!(name: 'my organisation without groups')
group_1 = Group.create!(name: 'group_1', shop_id: 1)
group_2 = Group.create!(name: 'group_2', shop_id: 2)
organisation_with_groups = Organisation.create!(name: 'my organisation with groups', groups: [group_1, group_2])

当我没有要包含的记录时

p Organisation.where(name: 'my organisation without groups').includes(:groups).where(groups: { shop_id: 1 })
# <ActiveRecord::Relation []>

当我有记录要包含时

p Organisation.where(name: 'my organisation with groups').includes(:groups).where(groups: { shop_id: 1 })
# <ActiveRecord::Relation [#<Organisation id: 2, name: "my organisation with groups", created_at: "2017-07-21 09:45:07", updated_at: "2017-07-21 09:45:07">]>

我得到的SQL是

puts Organisation.where(name: 'my organisation witReproduce the actual bug!hout groups').includes(:groups).where(groups: { shop_id: 1 }).to_sql

Where 子句在所有查询上执行，而不是在 LEFT OUTER JOIN 上执行

SELECT "organisations"."id" AS t0_r0,
       "organisations"."name" AS t0_r1,
       "organisations"."created_at" AS t0_r2,
       "organisations"."updated_at" AS t0_r3,
       "groups"."id" AS t1_r0,
       "groups"."name" AS t1_r1,
       "groups"."shop_id" AS t1_r2,
       "groups"."created_at" AS t1_r3,
       "groups"."updated_at" AS t1_r4
FROM "organisations"
LEFT OUTER JOIN "groups_organisations" 
             ON "groups_organisations"."organisation_id" = "organisations"."id"
LEFT OUTER JOIN "groups" 
             ON "groups"."id" = "groups_organisations"."group_id"
WHERE "organisations"."name" = 'my organisation without groups'
  AND "groups"."shop_id" = 1

我想要的是

LEFT OUTER JOIN "groups" 
             ON "groups"."id" = "groups_organisations"."group_id"
            AND groups.store_id = 20441
WHERE "organisations"."name" = 'my organisation without groups'

是否可以使用带条件的 LEFT OUTER JOIN 进行预加载？

Answer 1

你要选择groups.shop_id为1或者没有groups.shop_id的地方

你可以通过测试 nil

来做到这一点

Organisation.where(name: 'my organisation without groups').includes(:groups).where(groups: { shop_id: [nil, 1] })