ActiveRecordでSQLを書く

# join句

modelとmodelの関連性をmodelクラスに書く
```ruby
class User < ApplicationRecord
    # 関連の定義
    has_many :group_relations, primary_key: :user_id, foreign_key: :staff_id
end
```

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

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

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

## join条件 + α
``` sql
INNER JOIN A ON B = A.id = B.id AND B.enabled = true
```
の様に、カラム一致以外の条件を結合条件に含めたい場合は、直接書くしか無い

```ruby
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を使ってテーブル名にエイリアスを与える場合も直接書くしかない
``` ruby
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を使って書くと楽

```ruby
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
```

```ruby
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を使わない場合

```ruby
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`
```

とか

```ruby
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

下記の様に、県から地方単位に人口を集計したいとする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/156655/e06ee409-6ad5-2146-43bc-aedf3c05a20a.png)

これを一発で行うには

```sql
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でやると

```ruby
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を作ってやればすっきりして可読性は増す、気がする
```ruby
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)
}
```

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


## update

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/156655/307a6715-2d35-7c4c-1568-fb65e062e522.png)

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

```sql
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で実行する場合

```ruby
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ビルダーライブラリでは対応し切れないということなのだろうか？