Rails
ActiveRecord

Active Record テーブルの結合や結合したテーブルを利用した検索や集計やソートについて

Ruby on Rails Advent Calendar 2018 6日目です。

前置き

プログラミングを始めて1年と少し、
Railsを触り始めて8ヶ月ほどが経過しました。

その中で最もよく躓き、時間を取られたのが
「子レコードの条件で親レコードを検索したい」
「それぞれの親レコードが持つ子レコードを集計したい」
「子レコードの数で親レコードを並び替えたい」
そういった場面でActiveRecordのクエリメソッドを使ってクエリを組み立てる時でした。

ここではそんな時のクエリメソッドの組み立て方を書いていきます。
やむなく生のSQLを使用する場面もあるのですが
もし使用しなくても済む方法をご存知の方がいらっしゃいましたら
ご教示よろしくお願いします!
(Arelはシンタックスが難しすぎてわからなくなるので使いたくない...)

また、間違いなどあればすぐに訂正しますのでコメント宜しく願いします。

前提

以下のような紐づいた親子モデルがあるとする。

# Table name: parents
#
#  id                :bigint           primary key
#  created_at        :datetime
#  updated_at        :datetime

# Table name: children
#
#  id                :bigint           primary key
#  point             :integer
#  parent_id         :bigint
#  created_at        :datetime
#  updated_at        :datetime

class Parent < ApplicationRecord
  has_many :children
end

class Child < ApplicationRecord
  belongs_to :parent
end

テーブルの結合

INNER JOIN

joins(:table)でレシーバに引数のテーブルを内部結合できる

Parent.joins(:children)
SELECT "parents".* FROM "parents"
  INNER JOIN "children" ON "children"."parent_id" = "parents"."id"

LEFT OUTER JOIN

left_joins(:table)でレシーバに引数のテーブルを外部結合できる

Parent.left_joins(:children)
SELECT "parents".* FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id"

検索

結合したテーブルの条件で検索

結合したテーブルの条件で結合元を検索したい場合
where(結合したテーブル: { 条件 })
で検索することができる。

注)追記あり
この例は重複するので好ましくない場合があります。

Parent.joins(:children).where(children: { id: 1..3 })
SELECT "parents".* FROM "parents"
  INNER JOIN "children" ON "children"."parent_id" = "parents"."id"
  WHERE "children"."id" BETWEEN 1 AND 3

2018/12/7 追記

has_many な 関係のテーブルを JOIN して、一意でない条件で where 検索を行うと、
条件に当てはまるものが重複している場合そのまま重複して出力されるので、
例として好ましくありませんでした。

Parent.where(id: Child.where(id: 1..3).select(:parent_id))
SELECT "parents".* FROM "parents"
  WHERE "parents"."id" IN (
    SELECT "parent_id" FROM "children" WHERE "children"."id" IN (1..3)
  )

としてサブクエリを発行する形にすれば、重複なく親レコードを取得でき、
かつ複雑化した際に副作用が起きません。

@ighrs さんありがとうございます。勉強になります。


mergeで結合したテーブルのscopeを扱う

joinsleft_joinsを使った結合の便利なところは
結合したテーブルのscopeをmerge()で使えるところである。

この例もscopeの中身が先ほどのものと同様、
重複するので好ましくない場合があります。

class Child < ApplicationRecord
  belongs_to :parent
  scope :created_this_month, -> { where(created_at: Time.zone.today.all_month) }
end
Parent.joins(:children).merge(Child.created_this_month)
SELECT "parents".* FROM "parents"
  INNER JOIN "children" ON "children"."parent_id" = "parents"."id"
  WHERE "children"."created_at" BETWEEN '2018-12-01' AND '2018-12-31'

ちなみに

OR検索をする際はor()の中でもう一度テーブルを結合したものを用意しなければならない。

集計

子レコードをそれぞれいくつ持っているか集計する

内部結合にすることで子レコードを持っていないIDは集計されない

Parent.joins(:children).group(:id).count(:parent_id)
SELECT COUNT(parent_id) AS count_parent_id, "parents"."id" AS parents_id FROM "parents"
  INNER JOIN "children" ON "children"."parent_id" = "parents"."id"
  GROUP BY "parents"."id"

外部結合にすれば子レコードを持っていないIDも0として集計される。

Parent.left_joins(:children).group(:id).count(:parent_id)
SELECT COUNT(parent_id) AS count_parent_id, "parents"."id" AS parents_id FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id"
  GROUP BY "parents"."id"

条件を満たした子レコードの数を集計する

結合するテーブルのJOIN ONに絞り込み条件を入れる。
いろいろ調べてみたもののSQLで書かざるを得ないのかなって感じ。

Parent.joins(Arel.sql("LEFT OUTER JOIN children ON children.parent_id = parents.id AND children.created_at > '#{Time.zone.now.prev_month}'")).group(:id).count(:parent_id)
SELECT COUNT(parent_id) AS count_parent_id, "parents"."id" AS parents_id FROM "parents"
  LEFT OUTER JOIN children ON children.parent_id = parents.id AND absences.created_at > '2018-11-06 07:00:00 +0900'
  GROUP BY "parents"."id"

子レコードの特定カラムの合計をそれぞれ集計する

countsumに変えただけ

Parent.joins(:children).group(:id).sum(:point)
SELECT SUM(point) AS sum_point, "parents"."id" AS parents_id FROM "parents"
  INNER JOIN "children" ON "children"."parent_id" = "parents"."id"
  GROUP BY "parents"."id"

同じように外部結合にすれば子レコードを持っていなくても0として集計される。

ソート

orderに生のSQL入れないといけないのが気に入らない。
何かこううまく書ける方法知っている方いたら教えてください。

並び順は条件を指定しない場合「昇順(小さい順)」です。

ソートってことで、とりあえず外部結合して全親レコードをソートしていますが、
内部結合にした場合は、子レコードを持つ親レコードのみが抽出されてソートされます。

紐付く全ての子レコードの数でソートする

Parent.left_joins(:children).group(:id).order(Arel.sql('COUNT(children.id)'))
SELECT "parents".* FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id"
  GROUP BY "parents"."id" ORDER BY COUNT(children.id)

紐付く全ての子レコードの数でのソートに限って
counter_cacheを使う方がいいかもしれない。

class Child < ApplicationRecord
  belongs_to :parent, counter_cache: :children_count
end
class AddColumnToParent < ActiveRecord::Migration[5.2]
  def change
    add_column :parents, :children_count, :integer, null: false, default: 0
  end
end
Parent.order(Arel.sql('children_count'))
SELECT "parents".* FROM "parents" ORDER BY children_count

紐付く特定条件を満たした子レコードの数でソートする

こちらも結合するテーブルのJOIN ONに絞り込み条件を入れる。

Parent.joins(Arel.sql("LEFT OUTER JOIN children ON children.parent_id = parents.id AND children.created_at > '#{Time.zone.now.prev_month}'")).group(:id).order(Arel.sql('COUNT(children.id)'))
SELECT "parents".* FROM "parents"
  LEFT OUTER JOIN children ON children.parent_id = parents.id AND absences.created_at > '2018-11-06 07:00:00 +0900'
  GROUP BY "parents"."id" ORDER BY COUNT(children.id)

紐付く子レコードの特定カラムの値の合計でソートする

COUNTSUMに書き換えただけですねー。簡単簡単。

Parent.left_joins(:children).group(:id).order(Arel.sql('SUM(children.point)'))
SELECT "parents".* FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id"
  GROUP BY "parents"."id" ORDER BY SUM(children.point)

最後に

Ruby on Rails Advent Calendar 2018に参加させていただいたのが3日で
書く時間があまりなく...(言い訳)、少々雑な仕上がりになってしまい申し訳ありません。