`where` clauses in `has_* through:` scopes are not supported in history

[tagliala]

Models

class Country < ApplicationRecord
  include ChronoModel::TimeMachine

  has_many :cities, dependent: :destroy

  has_one :council
  has_one :first_member, through: :council
end

class Council < ApplicationRecord
  include ChronoModel::TimeMachine

  belongs_to :country

  has_many :members, dependent: :destroy

  # Problem is caused by `where` in the `scope`. `order` does not cause issues
  has_one :first_member, -> { where(active: true) }, class_name: 'Member', dependent: nil, inverse_of: false
end

class Member < ApplicationRecord
  include ChronoModel::TimeMachine

  belongs_to :council
  has_one :country, through: :council
end

Seeds

france = Country.create!(name: 'France')
council = france.create_council! name: 'Council'
council.members.create! name: 'Bob'
council.members.create! name: 'Alice'
council.members.create! name: 'Aaron', active: false

Output in the present

> Country.includes(:first_member).last
  Country Load (0.4ms)  SELECT "countries".* FROM "countries" ORDER BY "countries"."id" DESC LIMIT $1  [["LIMIT", 1]]
  SQL (0.8ms)  SELECT "councils"."id" AS t0_r0, "councils"."country_id" AS t0_r1, "councils"."name" AS t0_r2, "members"."id" AS t1_r0, "members"."council_id" AS t1_r1, "members"."name" AS t1_r2, "members"."active" AS t1_r3 FROM "councils" LEFT OUTER JOIN "members" ON "members"."active" = $1 AND "members"."council_id" = "councils"."id" WHERE "members"."active" = $2 AND "councils"."country_id" = $3  [["active", true], ["active", true], ["country_id", 1]]
=> #<Country:0x000000012d99f3d0 id: 1, name: "France", created_at: Sat, 25 May 2024 15:56:06.666591000 UTC +00:00, updated_at: Sat, 25 May 2024 15:56:06.666591000 UTC +00:00>

SELECT 
  "countries".* 
FROM 
  "countries" 
ORDER BY 
  "countries"."id" DESC 
LIMIT 
  $1 [[ "LIMIT", 
  1]]

SELECT 
  "councils"."id" AS t0_r0, 
  "councils"."country_id" AS t0_r1, 
  "councils"."name" AS t0_r2, 
  "members"."id" AS t1_r0, 
  "members"."council_id" AS t1_r1, 
  "members"."name" AS t1_r2, 
  "members"."active" AS t1_r3 
FROM 
  "councils" 
  LEFT OUTER JOIN "members" ON "members"."active" = $1 
  AND "members"."council_id" = "councils"."id" 
WHERE 
  "members"."active" = $2 
  AND "councils"."country_id" = $3 [[ "active", 
  true], 
  [ "active", 
  true], 
  [ "country_id", 
  1]]

Output in history

> Country.as_of(1.second.ago).includes(:first_member).last
  Country Load (1.4ms)  SELECT "countries".* FROM (SELECT "history"."countries".* FROM "history"."countries" WHERE ( '2024-05-25 16:03:39.485139'::timestamp <@ history.countries.validity )) "countries" ORDER BY "countries"."id" DESC LIMIT $1  [["LIMIT", 1]]
  SQL (1.1ms)  SELECT "councils"."id" AS t0_r0, "councils"."country_id" AS t0_r1, "councils"."name" AS t0_r2, "members"."id" AS t1_r0, "members"."council_id" AS t1_r1, "members"."name" AS t1_r2, "members"."active" AS t1_r3 FROM (SELECT "history"."councils".* FROM "history"."councils" WHERE "members"."active" = TRUE AND ( '2024-05-25 16:03:39.485139'::timestamp <@ history.councils.validity )) "councils" LEFT OUTER JOIN "members" ON "members"."active" = $1 AND "members"."council_id" = "councils"."id" WHERE "members"."active" = $2 AND "councils"."country_id" = $3  [["active", true], ["active", true], ["country_id", 1]]
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "members"
LINE 1: ...ory"."councils".* FROM "history"."councils" WHERE "members"....
                                                             ^

from ~/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.3/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params'
Caused by PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "members"
LINE 1: ...ory"."councils".* FROM "history"."councils" WHERE "members"....
                                                             ^

from ~/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.3/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params'

SELECT 
  "countries".* 
FROM 
  (
    SELECT 
      "history"."countries".* 
    FROM 
      "history"."countries" 
    WHERE 
      (
        '2024-05-25 16:03:39.485139' :: timestamp < @ history.countries.validity
      )
  ) "countries" 
ORDER BY 
  "countries"."id" DESC 
LIMIT 
  $1 [[ "LIMIT", 
  1]]

SELECT 
  "councils"."id" AS t0_r0, 
  "councils"."country_id" AS t0_r1, 
  "councils"."name" AS t0_r2, 
  "members"."id" AS t1_r0, 
  "members"."council_id" AS t1_r1, 
  "members"."name" AS t1_r2, 
  "members"."active" AS t1_r3 
FROM 
  (
    SELECT 
      "history"."councils".* 
    FROM 
      "history"."councils" 
    WHERE 
      "members"."active" = TRUE 
      AND (
        '2024-05-25 16:03:39.485139' :: timestamp < @ history.councils.validity
      )
  ) "councils" 
  LEFT OUTER JOIN "members" ON "members"."active" = $1 
  AND "members"."council_id" = "councils"."id" 
WHERE 
  "members"."active" = $2 
  AND "councils"."country_id" = $3 [[ "active", 
  true], 
  [ "active", 
  true], 
  [ "country_id", 
  1]]

Test case

```rb # frozen_string_literal: true require 'bundler/inline' gemfile(true) do source 'https://rubygems.org' gem 'chrono_model' # Test against latest Chronomodel: # gem 'chrono_model', github: 'ifad/chronomodel' gem 'pg' end require 'chrono_model' require 'minitest/autorun' require 'logger' # Needs a database called `chronomodel_test` ActiveRecord::Base.establish_connection(adapter: 'chronomodel', database: 'chronomodel_test') ActiveRecord::Base.logger = Logger.new($stdout) ActiveRecord::Schema.define do enable_extension :btree_gist create_table :countries, temporal: true, force: true do |t| t.string :name t.timestamps end create_table :councils, temporal: true, force: true do |t| t.references :country t.string :name t.timestamps end create_table :members, temporal: true, force: true do |t| t.references :council t.string :name t.boolean :active, default: true t.timestamps end end class Country < ActiveRecord::Base include ChronoModel::TimeMachine has_many :cities, dependent: :destroy has_one :council has_one :first_member, through: :council end class Council < ActiveRecord::Base include ChronoModel::TimeMachine belongs_to :country has_many :members, dependent: :destroy # Problem is caused by `where` in the `scope`. `order` does not cause issues has_one :first_member, -> { where(active: true) }, class_name: 'Member', dependent: nil, inverse_of: false end class Member < ActiveRecord::Base include ChronoModel::TimeMachine belongs_to :council has_one :country, through: :council end france = Country.create!(name: 'France') council = france.create_council! name: 'Council' council.members.create! name: 'Bob' council.members.create! name: 'Alice' council.members.create! name: 'Aaron', active: false class BugTest < Minitest::Test def test_historical_has_through_with_where_clause assert Country.as_of(Time.now).includes(:first_member).last end end ```

[tagliala]

Looks like that the preloader is creating this issue:

https://github.com/ifad/chronomodel/blob/0addc9ccfce27ffb461d6fc2662b2d67afaed19e/lib/chrono_model/patches/relation.rb#L14-L16

SELECT 
  "members".* 
FROM 
  (
    SELECT 
      "history"."members".* 
    FROM 
      "history"."members" 
    WHERE 
      (
        '2024-06-01 17:35:36.306466' :: timestamp < @ history.members.validity
      )
  ) "members" 
  INNER JOIN "councils" ON "members"."council_id" = "councils"."id" 
WHERE 
  "councils"."country_id" = $1 
  AND "members"."active" = $2 
LIMIT 
  $3 [[ "country_id", 
  1], 
  [ "active", 
  true], 
  [ "LIMIT", 
  1]]

[tagliala]

~Do we need a patch for active_record/associations/preloader/branch.rb?~

The problem is somewhere in the Preloader