ActiveRecordでSQLを書く

join句

modelとmodelの関連性をmodelクラスに書く

class User < ApplicationRecord
    # 関連の定義
    has_many :group_relations, primary_key: :user_id, foreign_key: :staff_id
end

primary_keyでテーブルAのカラム名を指定、
foreign_keyで接続するテーブルBのカラム名を指定

inner join

User.joins(:group_relations)
# SELECT `users`.* FROM `users` INNER JOIN `group_relations` ON 
# `group_relations`.`staff_id` = `users`.`user_id`

left outer join

User.left_joins(:group_relations)
# SELECT `users`.* FROM `users` LEFT OUTER JOIN `group_relations` ON
# `group_relations`.`staff_id` = `users`.`user_id`"

join条件 + α

INNER JOIN A ON B = A.id = B.id AND B.enabled = true

の様に、カラム一致以外の条件を結合条件に含めたい場合は、直接書くしか無い

User.joins("users.user_id = group_relations.staff_id AND enabled = true")
# SELECT `users`.* FROM `users` INNER JOIN group_relations ON
#  users.user_id = group_relations.staff_id AND enabled = true"

enabled = trueのようにイコールで書ける場合は良いが、
状況によって enabled IN (true, false)の様に配列になる場合は考えなければならない
サブクエリを使ったほうがきれいに書けるかも

alias

ASを使ってテーブル名にエイリアスを与える場合も直接書くしかない

User.joins("INNER JOIN group_relations AS r ON users.user_id = r.staff_id")
# SELECT `users`.* FROM `users` INNER JOIN group_relations AS r ON
#  users.user_id = r.staff_id

through

A join B かつ B join Cを書きたい時、throughを使って書くと楽

class User < ApplicationRecord
    has_many :groups, through: :group_relations
end

class GroupRelation < ApplicationRecord
    has_many :groups, primary_key: :group_id, foreign_key: :group_id
end

User.joins(:groups)

# SELECT `users`.* FROM `users`
#  INNER JOIN `group_relations` ON `group_relations`.`staff_id` = `users`.`user_id`
#  INNER JOIN `groups` ON `groups`.`group_id` = `group_relations`.`group_id`

throughを使わない場合

User.joins(groups: :group_relation)

# SELECT `users`.* FROM `users` 
#  INNER JOIN `group_relations` ON `group_relations`.`staff_id` = `users`.`user_id` 
#  INNER JOIN `groups` ON `groups`.`group_id` = `group_relations`.`group_id`

とか

User.joins(:group_relations).merge( GroupRelation.joins(:groups) )

# SELECT `users`.* FROM `users` 
#  INNER JOIN `group_relations` ON `group_relations`.`staff_id` = `users`.`user_id`
#  INNER JOIN `groups` ON `groups`.`group_id` = `group_relations`.`group_id

where句

CASE式

select, group

下記の様に、県から地方単位に人口を集計したいとする

これを一発で行うには

SELECT
  CASE prefecture_name
    WHEN '徳島' then '四国'
    WHEN '香川' then '四国'
    WHEN '愛媛' then '四国'
    WHEN '高知' then '四国'
    WHEN '福岡' then '九州'
    WHEN '佐賀' then '九州'
    WHEN '長崎' then '九州'
    ELSE 'その他'
  END AS district,
  sum(population)
FROM prefectures
GROUP BY `district`

と、CASE式を使い、group byでCASE式のエイリアスを指定してやれば可能
これをActiveRecordでやると

Population.select("
    case prefecture_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他'
    end as district,
    sum(population) as population
")
.group(:district)

selectの中をゴリゴリ書く必要がある... 🦍

scopeを作ってやればすっきりして可読性は増す、気がする

scope :per_region, -> (){
    select("
        case prefecture_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end as district
    ")
    .group(:district)
}

Population.per_region.select("sum(population) as population")

update

updateのset句でもCASEは使用できる

以下の様に、
1️⃣給料が30万以上の社員は、給料を10%減
2️⃣給料が25万以上28万未満の社員は、給料を20%増
という更新をしたい

1️⃣と2️⃣でupdate文を一回ずつ流すと、欲しい結果が得られないので失敗する
1回のupdateで終わらせるには、
set句でCASEを使用すれば可能

UPDATE salaries
SET salary = CASE 
  WHEN salary >= 300000 THEN salary * 0.9
  WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
  ELSE salary
END;

これをActiveRecordで実行する場合

Salary.update_all("
    salary = CASE 
      WHEN salary >= 300000 THEN salary * 0.9
      WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
      ELSE salary 
    END;
")

う～ん... そのまんま書き過ぎ？ ( ŏㅁŏ；)

効率的なクエリは複雑になるから、
逆にSQLビルダーライブラリでは対応し切れないということなのだろうか？