# ORM N+1 Problem

Harry Yuan

---

## ORM

Object Relational Mapping
(物件關聯對應)

---

ORM指的是一種「程式跟RDBMS的對應方法」、「寫法」
主要是提供使用者一個比較**方便**且**安全**的程式寫法，來存取資料庫。

---

select * from users where age = 30

↓

users = User.where(age: 30)

---

# 幹嘛用ORM?
<img src="https://i.imgur.com/tbdaup9.jpg =200x200" style="width:200px"></img>

<p style="text-align:center">這個是有什麼小用</p>

----

# :100:Pros

---

### :+1:加速開發
回傳的資料以物件呈現
可用喜好語言操作各個Item

----

### :+1:安全性
使用ORM就像是操作程式語言
一般都內建變數檢查，可以避免SQL injection攻擊

----

### :+1:簡化
程式碼重複性高時，比較好管理
也可包成method

----

### :+1: 方便更動資料庫
資料庫的Table有更動時，只要新增Migration檔案就好

---

# :x:ORM缺點

----

### :-1:效能
多了轉換「Code」→「SQL指令」的步驟。
大量code可能整體還是會慢一些。

----

### :-1:學習曲線較高
因為ORM語法跟SQL差很多
但已經懂物件導向的人就比較沒關係

----

### :-1:複雜查詢難維護
如果要做複雜查詢
ex: 使用sum, count, between等方法
**變成有時候還要嵌入原生SQL語法**

Article.select(:user_id, :likes).sum(:likes).group(:user_id)
User.where("age between ? and ?",10,30)
Article.select("id, user_id, sum(like_count) as total_likes").group("user_id")

---

### ORM的一些terms
Model: 模型，通常指一個可被透過ORM操控的table

class User <-> users

Migration: 一個檔案，宣告對資料庫的操作，如新增table、刪除table

create_table/update_column/add_index

Rollback: 復原DB至前一個migration版本

---

### Operation Flow


---

### Rollback if needed

---

## ORM N+1 problem
一言以蔽之：
_「以效率極糟的方式存取資料庫」_

---

### Scenario

今天我們讀書會10個人約小樹屋，突然大家想喝麥香，我們請hohshen去幫忙買
你是會？
1. hohshen下樓，一次買1罐，拿上來給1個朋友。再下去
2. hohshen下樓，買10罐上來給大家

---

## N+1就是說
我們叫hohshen下樓，明明可以1趟就買10罐，卻多跑9趟
User叫DB撈資料，明明可以1個query撈10筆資料，卻多query9次

---

#### Example

class Article < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :article
end

---

### Controller

class ArticlesController < ApplicationController
  def index
    @articles = Article.all
  end
end

<!-- article/index -->
<% @articles.each do |article| %>
  <div class="last-comment"><%=article.comments.last.content%>
  </div>
<% end %>

---

SELECT * FROM articles
SELECT * FROM comments WHERE comments.article_id = 1 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 2 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 3 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 4 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 5 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 6 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 7 order by id desc limit 1
SELECT * FROM comments WHERE comments.article_id = 50 order by id desc limit 1

---

# hohshen(DB)很累
只是看一個頁面，就跑出很多query


---

## Resolution

1. Raw SQL (then why do you use ORM?)
2. Use`Joins`/`Includes` Association

----

### Includes

class ArticlesController < ApplicationController
  def index
    @articles = Article.includes(:comments).all
  end
end

SELECT * FROM articles
SELECT * FROM comments WHERE comments.article_id = in (1,2,3,4,5,6,7,8...,50)

----

### Joins

class ArticlesController < ApplicationController
  def index
    @articles = Article.joins(:comments).all
  end
end

SELECT * FROM articles INNER JOIN comments ON comments.article_id = articles.id

---

## Experiences

### 實際可能會長怎樣？
老闆：我要熱門文章列表有
* 作者圖
* 文章標題
* 文章圖
* 最新留言者圖、內容、圖

---


---

class User < ActiveRecord::Base
  has_many :articles
  has_one :picture
end

class Article < ActiveRecord::Base
  has_many :comments
  has_one :picture
end

class Comment < ActiveRecord::Base
  belongs_to :user
  belongs_to :article
  has_one :picture
end

class Picture < ActiveRecord::Base
  belongs_to :pictureable, polymorphic: true
end

---

<% @articles.each do |article| %>
  <div class="wrap-article">
    <%= image_tag article.user.picture.asset.url, class: "author-picture" %>
    <%= image_tag article.picture.asset.url, class: "article-picture" %>
    <h1 class="article-title"><%= article.title, class: "article-title" %></h1>
    <p class="article-content"><%= article.content, class: "article-content" %></p>
  </div>

  <div class="wrap-last-comment">
    <%= image_tag article.comments.last, class: "commenter-picture" %>
    <% if article.comments.last %>
      <%= image_tag article.comments.last.picture.asset.url, class: "comment-picture" %>    
    <% end %>
    <span class="last-comment"><%= article.comments.last.message %></span>
  </div>
<% end %>

@articles = Article.includes(:picture,
            user: [:picture],
            comments:[{user: [:picture]}])

---

## 我是菜雞，找不到哪有N+1
可以用`Bullet gem`檢查

---

## Demo
###
1. Ruby老牌ORM Gem，一般都用於Rails開發使用
2. 提供class內association定義，從程式角度連結db關聯
3. 提供資料欄位validation
4. 提供各種callback methods以更彈性處理資料
* 資料庫支援
* PostgresQL
* MySQL
* SQLite

---

## 會用到的指令
<div style="text-align:left;">
rails g model User<br>
或是
rails g scaffold User<br>
<br>
後面都可以預先加欄位，如
rails g model User name:string tag_no:integer
<br>
<br><p style="background-color: #eeeeee;text-align:left;padding:8px;color:#5b5b5b;border-radius:6px;">
會產生
app/models/user.rb跟
db/migrations/202009171234_create_users.rb
</p>
rails db:migrate<br>
rails db:rollback
</div>

---

# Thank you

---

# Demo
實作article + comments

1. rails new npdemo
2. cd npdemo
3. bundle exec rails g scaffold User name:string
4. bundle exec rails g scaffold Article user:references title:string
5. bundle exec rails g scaffold Comment article:references user:references message:string
6. bundle exec rails db:migrate
7. bundle exec rails server

---

8. browse /users 
9. create user 1,2,3
10. browse /articles 
11. create user_id=1, user_id=2, user_id=3
12. browse /comments 
13. create (user_id=1,article_id=1), (user_id=2,article_id=2), (user_id=3, article_id=3)
14. go to /app/models/article.rb
15. add `has_many :comments`
16. go to /app/views/articles/index.html.erb
17. add \<td><%= article.comments.last.message%>\</td>
18. browse /articles
19. see rails server logs, you can see it queries comments for each article separately

---

20. go to /app/controllers/articles_controller.rb
21. see def index block
22. modify line to 

20. browse /articles
21. see rails server logs again, now you can see it queries comments for all articles with only 2 queries

## Done!


:::success
如果成功的話
:::


:::warning
記得要先煮水
:::