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

# 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:

chronomodel/lib/chrono_model/patches/relation.rb

Lines 14 to 16 in 0addc9c

	ActiveRecord::Associations::Preloader.new(
	records: records, associations: associations, scope: scope, model: model, as_of_time: as_of_time
	).call

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