includes with where clause in ActiveRecord

Question

I would like to preload associations but if no associations exist I would like to return the main relation.

To understand my issue here is a reproducible script :

# 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])

When I don't have records to include

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

When I have records to include

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">]>

The SQL I get is

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

Where clause is performed on all the query not on the 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

What I would like is

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

Is it possible to preload using LEFT OUTER JOIN with conditionals?

use `ActiveRecord::Associations::Preloader`: https://stackoverflow.com/questions/29695265/how-to-set-preload-scope-for-rails-associationspreloader — Pavel Mikhailyuk, Jul 21 '17 at 10:26
Thanks. As mention in https://github.com/rails/rails/issues/20704 *The Active Record Preloader API is private and not intended for public use.* :/ — Mio, Jul 21 '17 at 12:06
Yes, but it works and your main query still has ability to use `.select()`. When accepted answer are using trick and has no one. — Pavel Mikhailyuk, Jul 21 '17 at 13:10

score 6 · Accepted Answer · answered Jul 21 '17 at 10:21

6

You want to select where groups.shop_id is 1 or where there is no groups.shop_id

You can do that with a test for nil

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

answered Jul 21 '17 at 10:21

SteveTurczyn

36,057
6
41
53

I already thought about this solution but is it the result I want? I don't know – Mio Jul 21 '17 at 12:07

stevec · Answer 2 · 2022-06-06T11:50:39.430

For a general approach, this documentation on .includes() is very useful.

Especially the bit on conditions:

If you want to add string conditions to your included models, you'll have to explicitly reference them. For example:
User.includes(:posts).where('posts.name = ?', 'abcd')
Will throw an error, but this will work:
User.includes(:posts).where('posts.name = ?', 'abcd').references(:posts)
Note that includes works with association names while references needs the actual table name.

If you pass the conditions via hash, you don't need to call references explicitly, as where references the tables for you. For example, this will work:
User.includes(:posts).where(posts: { name: 'abcd' })

To take it one association further, if you had

thing.other_things.includes(:even_more_things)
.where(other_things: {col1: 'value'})

and you wanted to filter on even_more_things, you could use:

thing.other_things.joins(:even_more_things)
.where(even_more_things: { attribute: value })

includes with where clause in ActiveRecord

2 Answers2

Linked