LoginSignup
50
49

More than 5 years have passed since last update.

Ruby on RailsAdvent Calendar 2018Day 6
@rhiroe(Ryosuke Hiroe)

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

Last updated at Posted at 2018-12-05

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を扱う

joinsやleft_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('children.id')

SELECT COUNT(children.id) AS count_children_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('children.id')

SELECT COUNT(children.id) AS count_children_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で書かざるを得ないのかなって感じ。

12/24 指摘していただきました。(コメント参照)
DISTINCTは重複を除外する意味。
外部結合の場合、子レコードを持たない親レコードも行数としては一行持つ。
重複は除外されるのでカウントすべきは子レコードの一意なカラム。
今回は必ずJOINするのでincludesよりeager_loadの方が良さげ。
join("AND ...")のメソッドをひっ付けることで結合前に結合条件を追加できるみたい。

Parent.eager_load(:children).joins("AND children.created_at > '#{Time.zone.now.prev_month}'").group(:id).count('children.id')

SELECT COUNT(DISTINCT children.id) AS count_children_id, "parents"."id" AS parents_id FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id" AND children.created_at > '2018-11-7 07:00:00 UTC'
  GROUP BY "parents"."id"

ちなみに

モデルのアソシエーションに絞り込んだ物を用意しておくとシンプルに外部結合して集計できた。

# parent.rb
has_many :children_create_within_one_month,
         -> { where(created_at: Time.zone.now.prev_month...Time.zone.now },
         class_name: 'Child'

Parent.left_joins(:children_create_within_one_month).group(:id).count('children.id')

SELECT COUNT(children.id) AS count_children_id, "parents"."id" AS parents_id FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id" AND "children"."created_at"
  BETWEEN $1 AND $2 GROUP BY "parents"."id"

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

countをsumに変えただけ

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

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

集計と同じようにeager_load + joins("AND...")で結合しようとするとエラーが出る。
調べて原因がわかり次第追記します。

こちらも結合するテーブルの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 children.created_at > '2018-11-06 07:00:00 +0900'
  GROUP BY "parents"."id" ORDER BY COUNT(children.id)

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

COUNTをSUMに書き換えただけですねー。簡単簡単。

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日で
書く時間があまりなく...(言い訳)、少々雑な仕上がりになってしまい申し訳ありません。

50
49
10

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
50
49