has_oneを使って最もXXXのデータを取得する

Rails バックエンド

has_manyの関係性にあるデータを特定の条件で一意に絞り込みたいケースがあると思います。 今回はhas_oneを使って1対多のデータを1対1に関連付けする内容について書きます。

扱うデータ

本の貸出を題材に、著者（Author）、図書（Book）、貸出履歴（Rental）のschemaとModel、登録済みのデータを以下に記載します。

schema

ActiveRecord::Schema[7.0].define(version: 2024_01_23_040838) do
  enable_extension "plpgsql"

  create_table "authors", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "books", force: :cascade do |t|
    t.string "title"
    t.bigint "author_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["author_id"], name: "index_books_on_author_id"
  end

  create_table "rentals", force: :cascade do |t|
    t.bigint "book_id"
    t.bigint "user_id"
    t.date "rented_date"
    t.date "returned_date"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["book_id"], name: "index_rentals_on_book_id"
    t.index ["user_id"], name: "index_rentals_on_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  add_foreign_key "books", "authors"
  add_foreign_key "rentals", "books"
  add_foreign_key "rentals", "users"
end

Model

class Author < ApplicationRecord
  has_many :books, dependent: :destroy
end

class Book < ApplicationRecord
  belongs_to :author
  has_many :rentals, dependent: :destroy
end

class Rental < ApplicationRecord
  belongs_to :book
  belongs_to :user
end

データ

irb(main):023> Author.all.select(:id, :name).map(&:attributes)
  Author Load (0.8ms)  SELECT "authors"."id", "authors"."name" FROM "authors"
=> [{"id"=>1, "name"=>"夏目 漱石"}, {"id"=>2, "name"=>"太宰 治"}]

irb(main):024> Book.all.select(:id, :title, :author_id).map(&:attributes)
  Book Load (5.9ms)  SELECT "books"."id", "books"."title", "books"."author_id" FROM "books"
=> 
[{"id"=>1, "title"=>"坊っちゃん", "author_id"=>1},
 {"id"=>2, "title"=>"吾輩は猫である", "author_id"=>1},
 {"id"=>3, "title"=>"人間失格", "author_id"=>2},
 {"id"=>4, "title"=>"女生徒", "author_id"=>2}]

irb(main):025> Rental.all.select(:id, :book_id, :user_id, :rented_date).map(&:attributes)
  Rental Load (3.9ms)  SELECT "rentals"."id", "rentals"."book_id", "rentals"."user_id", "rentals"."rented_date" FROM "rentals"
=> 
[{"id"=>1, "book_id"=>2, "user_id"=>1, "rented_date"=>Tue, 01 Aug 2023},
 {"id"=>2, "book_id"=>4, "user_id"=>1, "rented_date"=>Fri, 01 Sep 2023},
 {"id"=>3, "book_id"=>1, "user_id"=>1, "rented_date"=>Sun, 01 Oct 2023},
 {"id"=>4, "book_id"=>3, "user_id"=>1, "rented_date"=>Wed, 01 Nov 2023}]

どういうデータを取り出すか

著者からみて自分の図書で「直近に貸りられた本」を取得してみたいと思います。

試行1：スコープブロック（引数あり）

author.rbに以下を追記します。 本毎に最後に借りられた日を求めjoinします。
スコープブロックに引数を取ると、関連元のオブジェクトが参照できます。

  has_one :recently_borrowed_book, lambda { |author|
    recently = Rental.group(:book_id).select(:book_id, 'MAX(rented_date) AS rented_date').to_sql
    author.books
          .joins("INNER JOIN (#{recently}) AS recently ON books.id = recently.book_id")
          .order('rented_date desc')

  }, class_name: :Book

rails consoleで動かしてみます。

irb(main):001> author = Author.find(1)
irb(main):002> author.recently_borrowed_book
  Book Load (12.5ms)  SELECT "books".* FROM "books" INNER JOIN (SELECT "rentals"."book_id", MAX(rented_date) AS rented_date FROM "rentals" GROUP BY "rentals"."book_id") AS recently ON books.id = recently.book_id WHERE "books"."author_id" = $1 ORDER BY rented_date desc LIMIT $2  [["author_id", 1], ["LIMIT", 1]]
=> 
#<Book:0x0000ffffa77cbd00
 id: 1,
 title: "坊っちゃん",
 author_id: 1,
 created_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00>

取得結果が正しいか確認してみます。

irb(main):005> author.books.joins(:rentals).order(rented_date: :desc).pluck('books.title', 'rentals.rented_date')
  Book Pluck (5.6ms)  SELECT "books"."title", "rentals"."rented_date" FROM "books" INNER JOIN "rentals" ON "rentals"."book_id" = "books"."id" WHERE "books"."author_id" = $1 ORDER BY "rented_date" DESC  [["author_id", 1]]
=> [["坊っちゃん", Sun, 01 Oct 2023], ["吾輩は猫である", Tue, 01 Aug 2023]]

正しそうです。
先ほどは一意に絞り込んだ著者から「直近借りられた本」を参照してみましたが、著者を絞り込まずpreloadを試してみます。

irb(main):007> Author.preload(:recently_borrowed_book)
  Author Load (5.0ms)  SELECT "authors".* FROM "authors"
  Book Load (1.5ms)  SELECT "books".* FROM "books" INNER JOIN (SELECT "rentals"."book_id", MAX(rented_date) AS rented_date FROM "rentals" GROUP BY "rentals"."book_id") AS recently ON books.id = recently.book_id WHERE "books"."author_id" = $1 AND "books"."author_id" = $2 ORDER BY rented_date desc  [["author_id", 1], ["author_id", 1]]
  Book Load (0.3ms)  SELECT "books".* FROM "books" INNER JOIN (SELECT "rentals"."book_id", MAX(rented_date) AS rented_date FROM "rentals" GROUP BY "rentals"."book_id") AS recently ON books.id = recently.book_id WHERE "books"."author_id" = $1 AND "books"."author_id" = $2 ORDER BY rented_date desc  [["author_id", 2], ["author_id", 2]]

著者の数だけ、Bookの取得が走ってますね。。
スコープブロックの引数にauthorを受け取りauthor.booksとしていることで、author毎にBookの取得が走っているようです。

試行2：スコープブロック（引数なし）

author.rbを以下のように修正します。
引数を取らずauthor.booksの記述をなくします。

  has_one :recently_borrowed_book, lambda {
    recently = Rental.group(:book_id).select(:book_id, 'MAX(rented_date) AS rented_date').to_sql
    joins("INNER JOIN (#{recently}) AS recently ON books.id = recently.book_id")
      .order('rented_date desc')

  }, class_name: :Book

動作確認してみます。

irb(main):001> author = Author.find(1)
irb(main):002> author.recently_borrowed_book
  Book Load (2.7ms)  SELECT "books".* FROM "books" INNER JOIN (SELECT "rentals"."book_id", MAX(rented_date) AS rented_date FROM "rentals" GROUP BY "rentals"."book_id") AS recently ON books.id = recently.book_id WHERE "books"."author_id" = $1 ORDER BY rented_date desc LIMIT $2  [["author_id", 1], ["LIMIT", 1]]
=> 
#<Book:0x0000ffff8f7cbd40
 id: 1,
 title: "坊っちゃん",
 author_id: 1,
 created_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00>

合ってます。
続いて著者を絞らずpreloadを試してみます。

irb(main):003> authors = Author.preload(:recently_borrowed_book)
  Author Load (3.6ms)  SELECT "authors".* FROM "authors"
  Book Load (2.1ms)  SELECT "books".* FROM "books" INNER JOIN (SELECT "rentals"."book_id", MAX(rented_date) AS rented_date FROM "rentals" GROUP BY "rentals"."book_id") AS recently ON books.id = recently.book_id WHERE "books"."author_id" IN ($1, $2) ORDER BY rented_date desc  [["author_id", 1], ["author_id", 2]]

irb(main):009> authors[0].recently_borrowed_book
=> 
#<Book:0x0000ffff8ef8af50
 id: 1,
 title: "坊っちゃん",
 author_id: 1,
 created_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00>
irb(main):010> authors[1].recently_borrowed_book
=> 
#<Book:0x0000ffff8ef8b338
 id: 3,
 title: "人間失格",
 author_id: 2,
 created_at: Tue, 23 Jan 2024 08:31:08.650864000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.650864000 UTC +00:00>

余分なBookの取得が減りました。 author_id: 1については結果が正しそうですが、author_id: 2についても確認してみます。

irb(main):011> authors[1].books.joins(:rentals).order(rented_date: :desc).pluck('books.title', 'rentals.rented_date')
  Book Pluck (3.5ms)  SELECT "books"."title", "rentals"."rented_date" FROM "books" INNER JOIN "rentals" ON "rentals"."book_id" = "books"."id" WHERE "books"."author_id" = $1 ORDER BY "rented_date" DESC  [["author_id", 2]]
=> [["人間失格", Wed, 01 Nov 2023], ["女生徒", Fri, 01 Sep 2023]]

結果は正しそうですが、1点気になる箇所がありました。
preloadのBookの取得は以下のSQLです。

SELECT
  "books".*
FROM "books"
INNER JOIN (
  SELECT
    "rentals"."book_id",
    MAX(rented_date) AS rented_date
  FROM "rentals"
  GROUP BY "rentals"."book_id"
) AS recently
  ON books.id = recently.book_id
WHERE
  "books"."author_id" IN ($1, $2)
ORDER BY rented_date desc

[["author_id", 1], ["author_id", 2]]

全ての著者が持つ図書の数分データ取得しています。
対象件数が膨れ上がると余計なデータトラフィックが増え、メモリオーバーフローに繋がるかもしれません。

試行3：NOT EXISTS でBookを一意に絞り込む

今度はauthor毎の取得するBookが一意になるよう、author.rbを以下のように変更してみます。 サブクエリで著者毎の「直近借りられた日」を出し、NOT EXISTSを使って同じ著者で「直近借りられた日」未満を除きます。

  has_one :recently_borrowed_book, lambda {
    recently = Rental.joins(book: :author).group(:author_id).select(:author_id, 'MAX(rented_date) AS rented_date').to_sql
    joins(:rentals)
      .where(
        <<~SQL.squish
          NOT EXISTS (
            SELECT 1
            FROM (#{recently}) AS recently
            WHERE
              rentals.rented_date < recently.rented_date
              AND books.author_id = recently.author_id
          )
        SQL
    )

  }, class_name: :Book

取得結果が正しいか確認してみます。

irb(main):001> author = Author.find(1)
irb(main):002> author.recently_borrowed_book
  Book Load (1.6ms)  SELECT "books".* FROM "books" INNER JOIN "rentals" ON "rentals"."book_id" = "books"."id" WHERE "books"."author_id" = $1 AND (NOT EXISTS ( SELECT 1 FROM (SELECT "author_id", MAX(rented_date) AS rented_date FROM "rentals" INNER JOIN "books" ON "books"."id" = "rentals"."book_id" INNER JOIN "authors" ON "authors"."id" = "books"."author_id" GROUP BY "author_id") AS recently WHERE rentals.rented_date < recently.rented_date AND books.author_id = recently.author_id )) LIMIT $2  [["author_id", 1], ["LIMIT", 1]]
=> 
#<Book:0x0000ffffb4ecc098
 id: 1,
 title: "坊っちゃん",
 author_id: 1,
 created_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00>

irb(main):004> authors = Author.preload(:recently_borrowed_book)
  Author Load (4.5ms)  SELECT "authors".* FROM "authors"
  Book Load (3.0ms)  SELECT "books".* FROM "books" INNER JOIN "rentals" ON "rentals"."book_id" = "books"."id" WHERE (NOT EXISTS ( SELECT 1 FROM (SELECT "author_id", MAX(rented_date) AS rented_date FROM "rentals" INNER JOIN "books" ON "books"."id" = "rentals"."book_id" INNER JOIN "authors" ON "authors"."id" = "books"."author_id" GROUP BY "author_id") AS recently WHERE rentals.rented_date < recently.rented_date AND books.author_id = recently.author_id )) AND "books"."author_id" IN ($1, $2)  [["author_id", 1], ["author_id", 2]]

irb(main):006> authors[0].recently_borrowed_book
=> 
#<Book:0x0000ffffb5f0f158
 id: 1,
 title: "坊っちゃん",
 author_id: 1,
 created_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00>
irb(main):007> authors[1].recently_borrowed_book
=> 
#<Book:0x0000ffffb5f0f7e8
 id: 3,
 title: "人間失格",
 author_id: 2,
 created_at: Tue, 23 Jan 2024 08:31:08.650864000 UTC +00:00,
 updated_at: Tue, 23 Jan 2024 08:31:08.650864000 UTC +00:00>

irb(main):012> recently = Rental.joins(book: :author).group(:author_id).select(:author_id, 'MAX(rented_date) AS rented_date').to_sql
irb(main):013> Book.joins(:rentals)
irb(main):014*       .where(
irb(main):015"         <<~SQL.squish
irb(main):016"           NOT EXISTS (
irb(main):017"             SELECT 1
irb(main):018"             FROM (#{recently}) AS recently
irb(main):019"             WHERE
irb(main):020"               rentals.rented_date < recently.rented_date
irb(main):021"               AND books.author_id = recently.author_id
irb(main):022"           )
irb(main):023*         SQL
irb(main):024>       )
  Book Load (5.1ms)  SELECT "books".* FROM "books" INNER JOIN "rentals" ON "rentals"."book_id" = "books"."id" WHERE (NOT EXISTS ( SELECT 1 FROM (SELECT "author_id", MAX(rented_date) AS rented_date FROM "rentals" INNER JOIN "books" ON "books"."id" = "rentals"."book_id" INNER JOIN "authors" ON "authors"."id" = "books"."author_id" GROUP BY "author_id") AS recently WHERE rentals.rented_date < recently.rented_date AND books.author_id = recently.author_id ))
=> 
[#<Book:0x0000ffffb4f110f8 id: 1, title: "坊っちゃん", author_id: 1, created_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00, updated_at: Tue, 23 Jan 2024 08:31:08.644662000 UTC +00:00>,
 #<Book:0x0000ffffb4f10ea0 id: 3, title: "人間失格", author_id: 2, created_at: Tue, 23 Jan 2024 08:31:08.650864000 UTC +00:00, updated_at: Tue, 23 Jan 2024 08:31:08.650864000 UTC +00:00>]

Authorに対してBookが一意になったので余計なデータ取得がなくなりました。

今回はhas_oneを使って1対多のデータを1対1に関連付けする内容についてまとめてみました。
associationを使うとクエリ発行回数を抑えることができますが、定義次第では意図しない挙動となるため注意して利用しようと思います。

誤った箇所などあればコメントでご指摘いただけると幸いです。