🌝

ActiveRecordの「table alias問題」に悩まされた結果gemを作って解消した話

2024/09/27に公開

3行で

  • できるだけ「文字列指定」ではなく「キー指定」を使いましょう
  • where句にてテーブル名を指定するのは極力避けましょう
  • activerecord-pretty-comparator gem を使うことで、文字列指定を使わざるを得なかった > もキー指定で書けます

はじめに

  • この記事では where('ends_at > ?', Time.current) のような書き方を「文字列指定」、 where(starts_at: ...Time.current) のような書き方を「キー指定」と呼びます
  • 株式会社グロービスのslackにかみぽさんにJOINいただいており、不定期にRailsの困りごとを 壁打ち/相談 させていただいています
  • この記事に出てくるコードは、実際のプロダクトコードをベースにしつつ問題を再現する最小のケースとして書いてみました
  • Kaigi on RailsにCFP出したもののrejectされてしまったので、CFPをベースにこの記事を書きました

作った経緯

※動かしてみて違いがわかりやすいようにペライチスクリプトにしているので、 association名 にバージョンをつけています

相談時の状態

前提

NOW = Time.new(2024, 9, 8, 9, 0, 0)

class User < ApplicationRecord
  has_many :active_plans_v1, -> {
    where(user_plans: { starts_at: ...NOW }).where('user_plans.ends_at > ?', NOW)
  }, class_name: 'UserPlan'
end

missing(:active_plans)の挙動

# SELECT
#   "users".*
# FROM
#   "users"
#   LEFT OUTER JOIN "user_plans" "active_plans_v1" ON "active_plans_v1"."user_id" = "users"."id"
#   AND "user_plans"."starts_at" < '2024-09-08 00:00:00'
#   AND (user_plans.ends_at > '2024-09-08 00:00:00')
# WHERE
#   "active_plans_v1"."id" IS NULL
User.where.missing(:active_plans_v1).to_sql
  • エラーが出る: no such column: user_plans.starts_at (SQLite3::SQLException)
    • missing(:active_plans_v1) の指定により table alias が active_plans_v1 となったため user_plans.starts_at は存在しない table alias を指定してしまっている

missing(:active_plans).joins(:user_plans)の挙動

# SELECT
#   "users".*
# FROM
#   "users"
#   INNER JOIN "user_plans" ON "user_plans"."user_id" = "users"."id"
#   LEFT OUTER JOIN "user_plans" "active_plans_v1" ON "active_plans_v1"."user_id" = "users"."id"
#     AND "user_plans"."starts_at" < '2024-09-08 00:00:00'
#     AND (user_plans.ends_at > '2024-09-08 00:00:00')
# WHERE
#   "active_plans_v1"."id" IS NULL
User.where.missing(:active_plans_v1).joins(:user_plans).to_sql
  • 本筋ではないが同名テーブルをJOINしたときの挙動も確認してみたところ、注意が必要なことがわかった
    • エラーは出ないものの、active_plans_v1 内のwhere(user_plans: { starts_at: ...NOW }).where("user_plans.ends_at > ?", NOW) の処理が .joins(:user_plans) にて active_plans_v1 という table alias ではなく、 user_plans の table alias に向いてしまっているため意図と違うSQLが生成されてしまう
      • where(user_plans: { starts_at: ...NOW })where("user_plans.ends_at > ?", NOW) のように user_plans というテーブル名を指定してしまっていることが原因
        • where(starts_at: ...NOW)where("ends_at > ?", NOW) のようにテーブル名を指定しない方が良い
          • もしテーブル名を指定した文字列指定 ( where('user_plans.ends_at > ?', NOW) )だと .joins(:user_plans) のテーブルを見てしまうため意図通りではない
            • SQL的には正しいためエラーにならず、かえって問題の発見が遅れるため非常に厄介(ペライチスクリプトにて、挙動を確認できるようにしてあります)

最初の修正

前提

NOW = Time.new(2024, 9, 8, 9, 0, 0)

class User < ApplicationRecord
  has_many :active_plans_v2_by_string, -> { active_v2_by_string }, class_name: 'UserPlan'
  has_many :active_plans_v2_by_symbol, -> { active_v2_by_symbol }, class_name: 'UserPlan'
end

class UserPlan < ApplicationRecord
  scope :active_v2_by_string, -> { where(starts_at: ...NOW).where('ends_at > ?', NOW) }
  scope :active_v2_by_symbol, -> { where(starts_at: ...NOW, ends_at: (NOW + 1.second)...) }
end
  • UserPlanモデルに有効期間を絞り込む処理を移し、UserモデルからUserPlanにて定義したscopeを呼び出すように変更した
    • 手元で挙動を確かめるために文字列指定の active_plans_v2_by_string とキー指定のactive_plans_v2_by_symbol で2パターン作ってみた

文字列指定(active_plans_v2_by_string)

# SELECT
#   "users".*
# FROM
#   "users"
#   LEFT OUTER JOIN "user_plans" "active_plans_v2_by_string" ON "active_plans_v2_by_string"."starts_at" < '2024-09-08 00:00:00'
#   AND "active_plans_v2_by_string"."user_id" = "users"."id"
#   AND (ends_at > '2024-09-08 00:00:00')
# WHERE
#   "active_plans_v2_by_string"."id" IS NULL
User.where.missing(:active_plans_v2_by_string).to_sql
  • テーブル名を指定しない文字列指定 ( where('ends_at < ?', NOW) )では ends_at < '2024-09-08 00:00:00' のようにテーブル名が指定されていないので、JOINしたときに同名カラムがあった場合にエラーになるリスクがある
    • user_plans.ends_at

キー指定(active_plans_v2_by_symbol)

# SELECT
#   "users".*
# FROM
#   "users"
#   LEFT OUTER JOIN "user_plans" "active_plans_v2_by_symbol" ON "active_plans_v2_by_symbol"."starts_at" < '2024-09-08 00:00:00'
#   AND "active_plans_v2_by_symbol"."ends_at" >= '2024-09-08 00:00:01'
#   AND "active_plans_v2_by_symbol"."user_id" = "users"."id"
# WHERE
#   "active_plans_v2_by_symbol"."id" IS NULL
User.where.missing(:active_plans_v2_by_symbol).to_sql
  • "active_plans_v2_by_symbol"."starts_at"という指定ができ意図通りに動くが、> を表現するのに ends_at: (NOW + 1.second)... という書き方が直感的ではない
    • とはいえ文字列指定をしてしまうと table aliasが意図通りに機能しない問題にあたってしまう…

activerecord-pretty-comparator gemを導入した修正

gemを作るまでの流れ

  1. ends_at: (NOW + 1.second)... という書き方が直感的ではないものの where('ends_at > ?', NOW) は table alias問題をしっかり踏んでしまうのでモヤモヤしていた
  2. かみぽさんに相談したところ、かみぽさんが以前 Railsにキー指定で比較演算子をできるようにする目玉機能をいれようとしていた ことを教えていただいた
  3. predicate生成に干渉できる拡張ポイントを用意 したことで、少しのコードで直感的な書き方を実現できることを教えていただいた
  4. かみぽさんにgem作成許可をいただき、育休中のスキマ時間を使って gem化してみるチャレンジをやってみることに
  5. かみぽさんにアドバイス・レビューいただき、育休終わり際に activerecord-pretty-comparator gem が完成!

前提

NOW = Time.new(2024, 9, 8, 9, 0, 0)

# activerecord-pretty-comparator gem 導入済み
class User < ApplicationRecord
  has_many :active_plans_v3, -> { active_v3 }, class_name: 'UserPlan'
end

class UserPlan < ApplicationRecord
  scope :active_v3, -> { where('starts_at <': NOW, 'ends_at >': NOW) }
end

activerecord-pretty-comparator gemを使用したキー指定

# SELECT
#   "users".*
# FROM
#   "users"
#   LEFT OUTER JOIN "user_plans" "active_plans_v3" ON "active_plans_v3"."starts_at" < '2024-09-08 00:00:00'
#   AND "active_plans_v3"."ends_at" > '2024-09-08 00:00:00'
#   AND "active_plans_v3"."user_id" = "users"."id"
# WHERE
#   "active_plans_v3"."id" IS NULL
User.where.missing(:active_plans_v3).to_sql
  • 途中であたった table alias 問題や書き方が直感的でない問題をクリアし、簡潔なコードになった

実際のスクリプト

ペライチで書いたのでぜひ手元で動かしてみてください

コード

https://gist.github.com/technuma/a99f1eec90218810b6012e0772840de8

# frozen_string_literal: true

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'

  gem 'activerecord'
  gem 'activerecord-pretty-comparator'
  gem 'sqlite3'
end

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
  end
  create_table :user_plans, force: true do |t|
    t.bigint :user_id
    t.datetime :starts_at
    t.datetime :ends_at
  end
end

NOW = Time.new(2024, 9, 8, 9, 0, 0) # for testing

class User < ActiveRecord::Base
  has_many :user_plans
  has_many :active_plans_v1, -> {
    where(user_plans: { starts_at: ...NOW }).where('user_plans.ends_at > ?', NOW)
  }, class_name: 'UserPlan'

  has_many :active_plans_v2_by_string, -> { active_v2_by_string }, class_name: 'UserPlan'
  has_many :active_plans_v2_by_symbol, -> { active_v2_by_symbol }, class_name: 'UserPlan'

  has_many :active_plans_v3, -> { active_v3 }, class_name: 'UserPlan'

  has_many :active_plans, -> { where('starts_at <': NOW, 'ends_at >': NOW) }, class_name: 'UserPlan'
end

class UserPlan < ActiveRecord::Base
  belongs_to :user
  scope :active_v2_by_string, -> { where(starts_at: ...NOW).where('ends_at > ?', NOW) }
  scope :active_v2_by_symbol, -> { where(starts_at: ...NOW, ends_at: (NOW + 1.second)...) }
  scope :active_v3, -> { where('starts_at <': NOW, 'ends_at >': NOW) }
end

user1, user2, user3, user4 = User.create!([{}, {}, {}, {}])

_expired_user_plans, = user1.user_plans.create!([
  { starts_at: 3.days.ago(NOW), ends_at: 2.days.ago(NOW) },
  { starts_at: 3.days.ago(NOW), ends_at: 1.days.ago(NOW) },
])

_active_user_plan = user2.user_plans.create!(
  starts_at: 3.days.ago(NOW), ends_at: 2.days.after(NOW)
)

_active_and_expired_user_plans, = user3.user_plans.create!([
  { starts_at: 3.days.ago(NOW), ends_at: 2.days.after(NOW) },
  { starts_at: 3.days.ago(NOW), ends_at: 1.days.ago(NOW) },
])

puts
puts '---active_plans_v1---'
p (User.where.missing(:active_plans_v1).pluck(:id) rescue $!)
p User.where.missing(:active_plans_v1).joins(:user_plans).pluck(:id)

puts
puts '---active_plans_v2_by_string---'
p User.where.missing(:active_plans_v2_by_string).pluck(:id)
p (User.where.missing(:active_plans_v2_by_string).joins(:user_plans).pluck(:id) rescue $!)

puts
puts '---active_plans_v2_by_symbol---'
p User.where.missing(:active_plans_v2_by_symbol).pluck(:id)
p User.where.missing(:active_plans_v2_by_symbol).joins(:user_plans).pluck(:id)

puts
puts '---active_plans_v3---'
p User.where.missing(:active_plans_v3).pluck(:id)
p User.where.missing(:active_plans_v3).joins(:user_plans).pluck(:id)

実行結果

technuma@mbp sample_code % ruby active_plan_references_detection.rb
Fetching gem metadata from https://rubygems.org/........
Resolving dependencies...
Using base64 0.2.0
Using sqlite3 2.0.4 (arm64-darwin)
Using timeout 0.4.1
Using bigdecimal 3.1.8
Using drb 2.2.1
Using logger 1.6.1
Using minitest 5.25.1
Using securerandom 0.3.1
Using bundler 2.4.10
Using concurrent-ruby 1.3.4
Using connection_pool 2.4.1
Using i18n 1.14.6
Using tzinfo 2.0.6
Using activesupport 7.2.1
Using activemodel 7.2.1
Using activerecord 7.2.1
Using activerecord-pretty-comparator 0.1.0
-- create_table(:users, {:force=>true})
D, [2024-09-23T11:59:10.716304 #72242] DEBUG -- :    (0.9ms)  DROP TABLE IF EXISTS "users"
D, [2024-09-23T11:59:10.716451 #72242] DEBUG -- :    (0.1ms)  CREATE TABLE "users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL)
   -> 0.0023s
-- create_table(:user_plans, {:force=>true})
D, [2024-09-23T11:59:10.716564 #72242] DEBUG -- :    (0.0ms)  DROP TABLE IF EXISTS "user_plans"
D, [2024-09-23T11:59:10.716645 #72242] DEBUG -- :    (0.0ms)  CREATE TABLE "user_plans" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" bigint, "starts_at" datetime(6), "ends_at" datetime(6))
   -> 0.0002s
D, [2024-09-23T11:59:10.717068 #72242] DEBUG -- :    (0.0ms)  CREATE TABLE "schema_migrations" ("version" varchar NOT NULL PRIMARY KEY)
D, [2024-09-23T11:59:10.717563 #72242] DEBUG -- :    (0.0ms)  CREATE TABLE "ar_internal_metadata" ("key" varchar NOT NULL PRIMARY KEY, "value" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL)
D, [2024-09-23T11:59:10.726178 #72242] DEBUG -- :   ActiveRecord::InternalMetadata Load (0.8ms)  SELECT * FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = ? ORDER BY "ar_internal_metadata"."key" ASC LIMIT 1  [[nil, "environment"]]
D, [2024-09-23T11:59:10.726390 #72242] DEBUG -- :   ActiveRecord::InternalMetadata Create (0.1ms)  INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES ('environment', 'default_env', '2024-09-23 02:59:10.726222', '2024-09-23 02:59:10.726223') RETURNING "key"
D, [2024-09-23T11:59:10.733260 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.733335 #72242] DEBUG -- :   User Create (0.1ms)  INSERT INTO "users" DEFAULT VALUES RETURNING "id"
D, [2024-09-23T11:59:10.733422 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.733606 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.733661 #72242] DEBUG -- :   User Create (0.1ms)  INSERT INTO "users" DEFAULT VALUES RETURNING "id"
D, [2024-09-23T11:59:10.733710 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.733819 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.733854 #72242] DEBUG -- :   User Create (0.0ms)  INSERT INTO "users" DEFAULT VALUES RETURNING "id"
D, [2024-09-23T11:59:10.733894 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.733994 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.734025 #72242] DEBUG -- :   User Create (0.0ms)  INSERT INTO "users" DEFAULT VALUES RETURNING "id"
D, [2024-09-23T11:59:10.734064 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.740554 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.740668 #72242] DEBUG -- :   UserPlan Create (0.1ms)  INSERT INTO "user_plans" ("user_id", "starts_at", "ends_at") VALUES (?, ?, ?) RETURNING "id"  [["user_id", 1], ["starts_at", "2024-09-20 02:59:10.726405"], ["ends_at", "2024-09-21 02:59:10.726405"]]
D, [2024-09-23T11:59:10.740734 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.741008 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.741059 #72242] DEBUG -- :   UserPlan Create (0.1ms)  INSERT INTO "user_plans" ("user_id", "starts_at", "ends_at") VALUES (?, ?, ?) RETURNING "id"  [["user_id", 1], ["starts_at", "2024-09-20 02:59:10.726405"], ["ends_at", "2024-09-22 02:59:10.726405"]]
D, [2024-09-23T11:59:10.741103 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.741409 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.741452 #72242] DEBUG -- :   UserPlan Create (0.1ms)  INSERT INTO "user_plans" ("user_id", "starts_at", "ends_at") VALUES (?, ?, ?) RETURNING "id"  [["user_id", 2], ["starts_at", "2024-09-20 02:59:10.726405"], ["ends_at", "2024-09-25 02:59:10.726405"]]
D, [2024-09-23T11:59:10.741492 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.741759 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.741798 #72242] DEBUG -- :   UserPlan Create (0.0ms)  INSERT INTO "user_plans" ("user_id", "starts_at", "ends_at") VALUES (?, ?, ?) RETURNING "id"  [["user_id", 3], ["starts_at", "2024-09-20 02:59:10.726405"], ["ends_at", "2024-09-25 02:59:10.726405"]]
D, [2024-09-23T11:59:10.741838 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction
D, [2024-09-23T11:59:10.742019 #72242] DEBUG -- :   TRANSACTION (0.0ms)  begin transaction
D, [2024-09-23T11:59:10.742058 #72242] DEBUG -- :   UserPlan Create (0.0ms)  INSERT INTO "user_plans" ("user_id", "starts_at", "ends_at") VALUES (?, ?, ?) RETURNING "id"  [["user_id", 3], ["starts_at", "2024-09-20 02:59:10.726405"], ["ends_at", "2024-09-22 02:59:10.726405"]]
D, [2024-09-23T11:59:10.742096 #72242] DEBUG -- :   TRANSACTION (0.0ms)  commit transaction

---active_plans_v1---
D, [2024-09-23T11:59:10.744326 #72242] DEBUG -- :   User Pluck (0.1ms)  SELECT "users"."id" FROM "users" LEFT OUTER JOIN "user_plans" "active_plans_v1" ON "active_plans_v1"."user_id" = "users"."id" AND "user_plans"."starts_at" < ? AND (user_plans.ends_at > ?) WHERE "active_plans_v1"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], [nil, "2024-09-23 02:59:10.726405"]]
#<ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: user_plans.starts_at>
D, [2024-09-23T11:59:10.744722 #72242] DEBUG -- :   User Pluck (0.1ms)  SELECT "users"."id" FROM "users" INNER JOIN "user_plans" ON "user_plans"."user_id" = "users"."id" LEFT OUTER JOIN "user_plans" "active_plans_v1" ON "active_plans_v1"."user_id" = "users"."id" AND "user_plans"."starts_at" < ? AND (user_plans.ends_at > ?) WHERE "active_plans_v1"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], [nil, "2024-09-23 02:59:10.726405"]]
[1, 1, 3]

---active_plans_v2_by_string---
D, [2024-09-23T11:59:10.744999 #72242] DEBUG -- :   User Pluck (0.0ms)  SELECT "users"."id" FROM "users" LEFT OUTER JOIN "user_plans" "active_plans_v2_by_string" ON "active_plans_v2_by_string"."starts_at" < ? AND "active_plans_v2_by_string"."user_id" = "users"."id" AND (ends_at > ?) WHERE "active_plans_v2_by_string"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], [nil, "2024-09-23 02:59:10.726405"]]
[1, 4]
D, [2024-09-23T11:59:10.745362 #72242] DEBUG -- :   User Pluck (0.0ms)  SELECT "users"."id" FROM "users" INNER JOIN "user_plans" ON "user_plans"."user_id" = "users"."id" LEFT OUTER JOIN "user_plans" "active_plans_v2_by_string" ON "active_plans_v2_by_string"."starts_at" < ? AND "active_plans_v2_by_string"."user_id" = "users"."id" AND (ends_at > ?) WHERE "active_plans_v2_by_string"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], [nil, "2024-09-23 02:59:10.726405"]]
#<ActiveRecord::StatementInvalid: SQLite3::SQLException: ambiguous column name: ends_at>

---active_plans_v2_by_symbol---
D, [2024-09-23T11:59:10.745630 #72242] DEBUG -- :   User Pluck (0.0ms)  SELECT "users"."id" FROM "users" LEFT OUTER JOIN "user_plans" "active_plans_v2_by_symbol" ON "active_plans_v2_by_symbol"."starts_at" < ? AND "active_plans_v2_by_symbol"."ends_at" >= ? AND "active_plans_v2_by_symbol"."user_id" = "users"."id" WHERE "active_plans_v2_by_symbol"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], ["ends_at", "2024-09-23 02:59:11.726405"]]
[1, 4]
D, [2024-09-23T11:59:10.745911 #72242] DEBUG -- :   User Pluck (0.0ms)  SELECT "users"."id" FROM "users" INNER JOIN "user_plans" ON "user_plans"."user_id" = "users"."id" LEFT OUTER JOIN "user_plans" "active_plans_v2_by_symbol" ON "active_plans_v2_by_symbol"."starts_at" < ? AND "active_plans_v2_by_symbol"."ends_at" >= ? AND "active_plans_v2_by_symbol"."user_id" = "users"."id" WHERE "active_plans_v2_by_symbol"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], ["ends_at", "2024-09-23 02:59:11.726405"]]
[1, 1]

---active_plans_v3---
D, [2024-09-23T11:59:10.746162 #72242] DEBUG -- :   User Pluck (0.0ms)  SELECT "users"."id" FROM "users" LEFT OUTER JOIN "user_plans" "active_plans_v3" ON "active_plans_v3"."starts_at" < ? AND "active_plans_v3"."ends_at" > ? AND "active_plans_v3"."user_id" = "users"."id" WHERE "active_plans_v3"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], ["ends_at", "2024-09-23 02:59:10.726405"]]
[1, 4]
D, [2024-09-23T11:59:10.746412 #72242] DEBUG -- :   User Pluck (0.0ms)  SELECT "users"."id" FROM "users" INNER JOIN "user_plans" ON "user_plans"."user_id" = "users"."id" LEFT OUTER JOIN "user_plans" "active_plans_v3" ON "active_plans_v3"."starts_at" < ? AND "active_plans_v3"."ends_at" > ? AND "active_plans_v3"."user_id" = "users"."id" WHERE "active_plans_v3"."id" IS NULL  [["starts_at", "2024-09-23 02:59:10.726405"], ["ends_at", "2024-09-23 02:59:10.726405"]]
[1, 1]
GLOBIS Tech

Discussion