iT邦幫忙

2023 iThome 鐵人賽
DAY 23
0
Software Development

從零開始，在 coding 路上的 30 個為什麼？不對！是無數個為什麼！系列 第 23 篇

Day 23 - 理解 Ruby on Rails，Active Record Query （下）？

15th鐵人賽 ruby on rails query
141 瀏覽

  • 分享至 

  • xImage
    •  

繼續來探究 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 方法！

    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` 來達到目的：

    ```ruby
    user_counts = User.group(:role).having("COUNT(*) > ?", 3).count
    ```

    先使用 `group(:role)` 按照 `role` 分組用戶。
    然後，使用 `having("COUNT(*) > ?", 3)` 條件，僅保留那些擁有超過3個用戶的角色。
    最後，我們使用 `count` 方法計算每個符合條件的角色的用戶數量。

    生成的 SQL 查詢：
    ```sql
    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` 子句->
       ```sql
       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` 子句->
       ```sql
       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

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

    ```ruby
    # app/controllers/users_controllers.rb
    class UsersController < ApplicationController
      def index
        @users = User.all
      end
    end
    ```
    ```html
    # 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：

      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，並且希望獲得每個用戶的所有貼文，即使有些用戶還沒有。

    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!（歡迎參觀 ୧ʕ•̀ᴥ•́ʔ୨）


上一篇
Day 22 - 理解 Ruby on Rails，Active Record Query （上）？
下一篇
Day 24 - 理解 Ruby on Rails，Scope 前情提要 Block、Proc 和 Lambda!
系列文
從零開始，在 coding 路上的 30 個為什麼？不對！是無數個為什麼！ 共 30 篇
  1. 26
    Day 26 - 理解 Ruby on Rails，Active Record Query - Enum 是什麼？
  2. 27
    Day 27 - 理解 Database - 資料庫是什麼？
  3. 28
    Day 28 - 理解 Database - 資料庫正規化與反正規化！
  4. 29
    Day 29 - 理解 Database - 資料庫 Transaction 與 ACID！
  5. 30
    Day 30 - 理解 Git - 快速整裡出的 Git 常見指令！
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙