🌝
ActiveRecordの「table alias問題」に悩まされた結果gemを作って解消した話
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的には正しいためエラーにならず、かえって問題の発見が遅れるため非常に厄介(ペライチスクリプトにて、挙動を確認できるようにしてあります)
- もしテーブル名を指定した文字列指定 (
-
-
- エラーは出ないものの、active_plans_v1 内の
最初の修正
前提
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を作るまでの流れ
-
ends_at: (NOW + 1.second)...
という書き方が直感的ではないもののwhere('ends_at > ?', NOW)
は table alias問題をしっかり踏んでしまうのでモヤモヤしていた - かみぽさんに相談したところ、かみぽさんが以前 Railsにキー指定で比較演算子をできるようにする目玉機能をいれようとしていた ことを教えていただいた
- predicate生成に干渉できる拡張ポイントを用意 したことで、少しのコードで直感的な書き方を実現できることを教えていただいた
- かみぽさんにgem作成許可をいただき、育休中のスキマ時間を使って gem化してみるチャレンジをやってみることに
- かみぽさんにアドバイス・レビューいただき、育休終わり際に 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]
Discussion