Viiisit [Ruby on Rails] - Active Record Query (下)!

繼續來探究 Active Record 在抓取資料上的方法！
繼上篇，我們已經學習了如何進行基本的資料查詢。
這篇將深入介紹一些查詢方法以及常見的解決 N+1 問題該如何解決，Let’s go!

Grouping

要在查詢 SQL 中應用 GROUP BY 子句，可以使用 group 方法。

SQL 中的 GROUP BY 子句用於將資料庫中的記錄按照一個或多個欄位的值分組，可以對每個不同組的記錄應用聚合函數，例如計算總和 SUM() 、平均值 AVG() 或計數 COUNT()。

GROUP BY 子句通常與聚合函數一起使用，以便對分組的記錄執行統計操作，有助於整理和分析資料，並生成有意義的統計結果。

group, count
建立一個 User 模型，每個用戶都有一個 role，
表示他們的角色 (例如：admin, user, editor)，現在想要統計每個角色的用戶數量。
我們便可以使用 group, count 方法！
1
User.group(:role).count
這個查詢將返回一個 Hash，其中每個 key 代表一個不同的角色，而每個 value 代表該角色的用戶數量。將具有相同 role 值的用戶記錄合併在一起，然後計算每個組的記錄數。
生成的 SQL 查詢：SELECT role, COUNT(*) AS count FROM users GROUP BY role
Having Conditions

SQL 使用 HAVING 子句，用於對 GROUP BY 子句分組後的結果集進行篩選，
並返回符合條件的分組結果。

想在 Active Record 查詢中使用 GROUP BY 和 HAVING 條件結合時，
可以使用 group 和 having 方法！

建立一個 User 模型，要統計每個角色的用戶數量，但只保留那些擁有超過一定數量的用戶的結果，
可以使用 group 和 having 來達到目的：
1
user_counts = User.group(:role).having("COUNT(*) > ?", 3).count
先使用 group(:role) 按照 role 分組用戶。
然後，使用 having("COUNT(*) > ?", 3) 條件，僅保留那些擁有超過3個用戶的角色。
最後，我們使用 count 方法計算每個符合條件的角色的用戶數量。
生成的 SQL 查詢：
1
2
3
4
SELECT role, COUNT(*) AS count
FROM users
GROUP BY role
HAVING COUNT(*) > 3
SQL 裡，HAVING 子句與 WHERE 子句的差別：
WHERE 子句用於對原始數據（未分組）進行篩選，並返回符合條件的個別記錄（在查詢之前應用）
HAVING 子句用於對 GROUP BY 子句分組後的結果集進行篩選，並返回符合條件的分組結果。（在查詢之後應用）

假設有一個 orders 表，
其中包含了每筆訂單的紀錄，包括訂單金額（order_amount）和客戶 ID（customer_id）。
找出每個客戶的總訂單金額超過 1000 的客戶。
- 使用 WHERE 子句->
  1
  2
  3
  4
  SELECT customer_id, SUM(order_amount) AS total_amount
  FROM orders
  GROUP BY customer_id
  WHERE total_amount > 1000;
  WHERE 子句直接應用於原始數據，即每筆訂單的記錄。
  但這樣的查詢會引發錯誤，因為 total_amount 是在 SELECT 子句中定義的，
  而不是在 WHERE 子句中。
- 改使用 HAVING 子句->
  1
  2
  3
  4
  SELECT customer_id, SUM(order_amount) AS total_amount
  FROM orders
  GROUP BY customer_id
  HAVING SUM(order_amount) > 1000;
  HAVING 子句應用於已分組的結果，即每個客戶的總訂單金額。
  我們在 HAVING 子句中使用 SUM(order_amount) 來篩選總訂單金額超過 1000 的客戶。

Eager Loading Associations 預先載入

Eager Loading Associations 是指在一次性查詢中預先載入一個或多個關聯模型的數據，以減少在後續操作中的查詢次數，從而提高應用程式的性能，通常用於解決 N+1 查詢問題。

N+1 查詢問題是什麼？

N+1 查詢問題是在 Active Record 查詢中常見的效能問題之一，通常出現在處理關聯數據的時候。
現在有兩個 Model User 和 Post ，他們之間有一個一對多的關係。

每個用戶可以發佈多篇貼文：

class User < ApplicationRecord
  has_many :posts
end

class Post < ApplicationRecord
  belongs_to :user
end

列出所有用戶以及每個用戶的貼文。

# app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.all
  end
end

# app/views/users/index.html.erb
<% @users.each do |user| %>
  <h2><%= user.name %></h2>
  <ul>
    <% user.posts.each do |post| %>
      <li><%= post.title %></li>
    <% end %>
  </ul>
  <hr>
<% end %>

開始列出每個用戶的每個貼文標題時，每次呼叫 user.posts.each 時都會觸發一次額外的資料庫查詢，以獲取該用戶的貼文標題。當去查看 log 就會看到以下訊息，先找到所有 users，然後開始找每個用戶的每篇貼文，數一下就會發現總共做了 11 次查詢。這就是 N+1 查詢問題 （有 10 個用戶，就會先 1（找出 10 個用戶）+ 10（每個貼文都去找關連的用戶）= 總共 11 次查詢）

User Load (1.3ms)  SELECT "users".* FROM "users"
  ↳ app/views/users/index.html.erb:1
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 1]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 2]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 3]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 4]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 5]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 6]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 7]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 8]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 9]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 10]]
  ↳ app/views/users/index.html.erb:4

解決 N+1 查詢問題的方法

想要解決這樣的問題就要來應用 Eager Loading Associations，可以使用 includes 方法，讓 Active Record 確保所有指定的關聯，加載的查詢減到最少。除了 includes 方法，以下會說明其他種方式，來避免 N+1 查詢問題。

includes （Eager Loading Associations）
將剛剛在 users_controllers.rb 的方法改寫為 @users = User.includes(:posts)

# app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.includes(:posts)
  end
end

查看 log：

User Load (0.2ms)  SELECT "users".* FROM "users"
↳ app/views/users/index.html.erb:1
Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]
↳ app/views/users/index.html.erb:1

可以發現 SQL 查詢從 posts 表中查詢的方式不同了！
具體來說，裡面使用了 IN 來指定多個可能的 user_id，
通過 user_id 來查找屬於用戶 1、2、3、4、5、6、7、8、9 和 10 的貼文，
而這些 user_id 是通過問號（?）參數傳遞的，這種參數化的查詢是防止 SQL injection。
透過只發出一個 SQL 查詢而不是 N 個，提高效能，解決 N+1 查詢問題。

preload （Eager Loading Associations）
將剛剛在 users_controllers.rb 的方法改寫為 @users = User.preload(:posts)

# app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.preload(:posts)
  end
end

查看 log：

User Load (0.9ms)  SELECT "users".* FROM "users"
↳ app/views/users/index.html.erb:1
Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]
↳ app/views/users/index.html.erb:1

可以看出 preload 跟 includes 是能達到相同效果的！
但要注意的是：
preload 不能指定條件，無法針對這些關聯數據進行進一步的條件過濾。
includes 可以指定條件，以檢索符合條件的相關數據。

eager_load （Eager Loading Associations）
將剛剛在 users_controllers.rb 的方法改寫為 @users = User.eager_load(:posts)

# app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.eager_load(:posts)
  end
end

查看 log：

1
2

SQL (1.3ms)  SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."created_at" AS t1_r3, "posts"."updated_at" AS t1_r4 FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
↳ app/views/users/index.html.erb:1

使用 eager_load，Active Record 會使用 LEFT OUTER JOIN 來載入所有指定的關聯。
LEFT OUTER JOIN 是 SQL 中的一種 JOIN 類型，用於結合兩個或多個表中的數據，以便在結果中檢索相關數據。具體來說，LEFT OUTER JOIN 返回左側表中的所有行，並返回右側表中與左側表中的行相匹配的行。如果右側表中沒有與左側表匹配的行，則返回 NULL 值。
假設你有兩個表：Users 和 Posts，並且希望獲得每個用戶的所有貼文，即使有些用戶還沒有。
1
2
3
SELECT Users.name, Posts.post_number
FROM Users
LEFT OUTER JOIN Posts ON Users.id = Posts.user_id;
在這個查詢中，Users 表是左側表，Posts 表是右側表。
ON Users.id = Posts.user_id 指定了表之間的關聯條件。結果將返回所有用戶的名稱以及他們的貼文，如果某個用戶沒有，則貼文將顯示為 NULL。

來比較一下 includes 和 eager_load 差異：
includes:

includes 使用兩個 SQL 查詢，
一個用於查詢主 Model User，另一個用於查詢關聯 Model Post。
適用於需要在遍歷主 Model 的集合時搜尋關聯數據的情況，這樣他不需要額外的查詢。

eager_load:

eager_load 使用一個 SQL 查詢，
將主 Model 和關聯 Model 的數據一次性載入，並使用 JOIN 連接他們。
適用於需要在遍歷主 Model 的集合之前搜尋關聯數據的情況，
這樣他可以通過 JOIN 執行一次性的複雜查詢。

參考資料：
➫ Active Record Query Interface_
➫ Active Record 查詢
➫ PJCHENder - [Rails] Active Record Query（SQL Query & Model 資料查詢）
➫ Preload, Eagerload, Includes and Joins
➫ Ruby on Rails - 用 Include 和 Join 避免 N+1 Query

Viiisit [Ruby on Rails] - Active Record Query (下)!

Grouping

Eager Loading Associations 預先載入

N+1 查詢問題是什麼？

解決 N+1 查詢問題的方法

Viiisit [Ruby on Rails] - Scope 前情提要 Block、Proc 和 Lambda!

Viiisit [Ruby on Rails] - Active Record Query (上)!