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

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

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

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

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

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

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

```ruby
# 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)`でレシーバに引数のテーブルを内部結合できる

```ruby
Parent.joins(:children)
```
```sql
SELECT "parents".* FROM "parents"
  INNER JOIN "children" ON "children"."parent_id" = "parents"."id"
```
##LEFT OUTER JOIN
`left_joins(:table)`でレシーバに引数のテーブルを外部結合できる

```ruby
Parent.left_joins(:children)
```
```sql
SELECT "parents".* FROM "parents"
  LEFT OUTER JOIN "children" ON "children"."parent_id" = "parents"."id"
```
#検索
##結合したテーブルの条件で検索
結合したテーブルの条件で結合元を検索したい場合
`where(結合したテーブル: { 条件 })`
で検索することができる。

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

```ruby
Parent.joins(:children).where(children: { id: 1..3 })
```
```sql
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 検索を行うと、
条件に当てはまるものが重複している場合そのまま重複して出力されるので、
例として好ましくありませんでした。 

```ruby
Parent.where(id: Child.where(id: 1..3).select(:parent_id))
```
```sql
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の中身が先ほどのものと同様、
重複するので好ましくない場合があります。*

```ruby
class Child < ApplicationRecord
  belongs_to :parent
  scope :created_this_month, -> { where(created_at: Time.zone.today.all_month) }
end
```
```ruby
Parent.joins(:children).merge(Child.created_this_month)
```
```sql
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は集計されない

```ruby
Parent.joins(:children).group(:id).count('children.id')
```
```sql
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として集計される。

```ruby
Parent.left_joins(:children).group(:id).count('children.id')
```
```sql
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 ...")`のメソッドをひっ付けることで結合前に結合条件を追加できるみたい。*

```ruby
Parent.eager_load(:children).joins("AND children.created_at > '#{Time.zone.now.prev_month}'").group(:id).count('children.id')
```
```sql
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"
```

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

```ruby
# parent.rb
has_many :children_create_within_one_month,
         -> { where(created_at: Time.zone.now.prev_month...Time.zone.now },
         class_name: 'Child'
```
```ruby
Parent.left_joins(:children_create_within_one_month).group(:id).count('children.id')
```
```sql
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`に変えただけ

```ruby
Parent.joins(:children).group(:id).sum(:point)
```
```sql
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入れないといけないのが気に入らない。
何かこううまく書ける方法知っている方いたら教えてください。

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

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

```ruby
Parent.left_joins(:children).group(:id).order(Arel.sql('COUNT(children.id)'))
```
```sql
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`を使う方がいいかもしれない。

```ruby
class Child < ApplicationRecord
  belongs_to :parent, counter_cache: :children_count
end
```
```ruby
class AddColumnToParent < ActiveRecord::Migration[5.2]
  def change
    add_column :parents, :children_count, :integer, null: false, default: 0
  end
end
```
```ruby
Parent.order(Arel.sql('children_count'))
```
```sql
SELECT "parents".* FROM "parents" ORDER BY children_count
```
##紐付く特定条件を満たした子レコードの数でソートする
*集計と同じように`eager_load` + `joins("AND...")`で結合しようとするとエラーが出る。
調べて原因がわかり次第追記します。*

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

```ruby
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)'))
```
```sql
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`に書き換えただけですねー。簡単簡単。

```ruby
Parent.left_joins(:children).group(:id).order(Arel.sql('SUM(children.point)'))
```
```sql
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日で
書く時間があまりなく...(言い訳)、少々雑な仕上がりになってしまい申し訳ありません。