iT邦幫忙

2021 iThome 鐵人賽
DAY 30
0
Modern Web

初階 Rails 工程師的養成系列 第 30 篇

Day30. Model 與關聯 - preload, join, includes 一次釐清

13th鐵人賽 ruby rails orm model
1830 瀏覽

  • 分享至 

  • xImage
    •  

今天我們會用部落格跟使用者的關係來講解關聯，首先先做設定，部落格跟使用者的關係為

  • 使用者對應多個部落格
  • 每一個部落格對應使用者
class Blog < ApplicationRecord
  belongs_to :user
end

class User < ApplicationRecord
  has_many :blogs
end

今天我們的主題是

  • 一對一 & 一對多之間的關係
  • 用情境講解join, preload, includes, left_joins, eager_load, sub query 的用法

一對一 & 一對多

在關聯式資料庫的關係裡面，一共有1對1、1對多、多對多三種關聯，而我們先從1對多開始破題

user.blogs

我們看id=3的使用者對應的部落格

user = User.find 3

user.blogs
#=> Blog Load (1.9ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` = 3 LIMIT 11

#======== 上面的意思與下面的相同
Blog.where(user: user)
#=> Blog Load (1.9ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` = 3 LIMIT 11
Blog.where(user_id: user.id)
#=> Blog Load (1.9ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` = 3 LIMIT 11

blog.user

第1篇部落格對應的使用者

blog = Blog.first

> blog.user
#=>   SELECT `blogs`.* FROM `blogs` ORDER BY `blogs`.`id` ASC LIMIT 1
#=>  SELECT `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1

當我們要在id=3的使用者創建一筆新的部落格

user = User.find 3

#==== 有bang! ➡️ 引發錯誤
user.blogs.create!(title: '標題4', content: '內文4')
#=> ActiveRecord::RecordInvalid (校驗失敗: Genre 不能為空白)

#==== 沒有bang! ➡️ 不會引發錯誤
user.blogs.create(title: '標題4', content: '內文4')
#=> #<Blog id: nil, title: "標題4", content: "內文4", user_id: 3, genre: nil, created_at: nil, updated_at: nil>

#====== 創建成功
user.blogs.create!(title: '標題4', content: '內文4', genre: :technology)
# (16.8ms)  BEGIN
#====  Blog Create (171.3ms)  INSERT INTO `blogs` (`title`, `content`, `user_id`, `genre`, `created_at`, `updated_at`) VALUES ('標題4', '內文4', 3, 2, '2021-0915:25:27.228691', '2021-09-24 15:25:27.228691')
# (76.9ms)  COMMIT
=> #<Blog id: 4, title: "標題4", content: "內文4", ...">

我們舉一個1對1的例子，如訂單跟發票的關係。

class Invoice < ApplicationRecord
  belongs_to :order
end

class Order < ApplicationRecord
  has_one :invoice
end

當我們要建立發票的寫法時，1對1的寫法會不一樣，1對1寫法為加入前綴 create_

order = Order.first

#==== correct
order.create_invoice!(customer_name: 'han', customer_phone: '0912559059', customer_email: 'k445566778899k@gmail.com', price: 100)

#==== incorrect
order.invoices.create!(customer_name: 'han', customer_phone: '0912559059', customer_email: 'k445566778899k@gmail.com', price: 100)

若我們要找訂單的發票，或者是找發票的訂單，可以用以下寫法

order = Order.first
order.invoice
#=> SELECT `invoices`.* FROM `invoices` WHERE `invoices`.`order_id` = 2 LIMIT 1

invoice = Invoice.first
invoice.order
#=> SELECT `orders`.* FROM `orders` WHERE `orders`.`id` = 1106 LIMIT 1

想到自己曾經在面試的時候，有被詢問到關於1對1關聯，兩者之間有何區別？

order.invoice
invoice.order

我們看SQL Statement，可以看出有一個不同的地方在於invoice已經存取了order.id，因此可以直接用id=1106去找到訂單，而order要找invoice必須用invoice_id去查找。

關聯資料表查詢

下列舉若干比較複雜的例子說明

查詢有文章的使用者

當我們要查有哪些使用者寫部落格文章

#===== 找出有寫部落格的使用者
User.joins(:blogs).where('blogs.id IS NOT NULL')
#=> User Load (6.0ms)  SELECT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) LIMIT 11

使用Join會不小心撈到多筆資料。以下面的例子來說，由於有三篇文章都關聯到某一個作者，因此會撈到3筆同一個作者的資料

User.joins(:blogs).where('blogs.id IS NOT NULL').count       #=> 3
User.joins(:blogs).where('blogs.id IS NOT NULL').uniq.count  #=> 1

要如何解決這個方式？除了uniq，我們可以使用distinct，distinct為Orm的語法

User.joins(:blogs).where('blogs.id IS NOT NULL').distinct
#=> SELECT DISTINCT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE blogs.id IS NOT NULL LIMIT 11

我們看到ransack 在是以 SubQuery 來處理。明天會講到ransack

User.ransack(blogs_id_not_null: true).result
#=>  User Load (1.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (SELECT `blogs`.`user_id` FROM `blogs` WHERE `blogs`.`user_id` = `users`.`id` AND `blogs`.`id` IS NOT NULL) LIMIT 11

=> #<ActiveRecord::Relation [#<User id: 3, ...>]>

外面的 query 包覆裡面的 query的寫法，統稱sub query

-- first query
SELECT `blogs`.`user_id` FROM `blogs` WHERE `blogs`.`user_id` = `users`.`id` AND `blogs`.`id` IS NOT NULL

-- second query
SELECT `users`.* FROM `users` WHERE `users`.`id` IN ( FIRST_QUERY ) LIMIT 11

除了join以外，還可以使用includes。

User.includes(:blogs).where('blogs.id IS NOT NULL')
# User Load (8.3ms)  SELECT `users`.* FROM `users` WHERE blogs.id IS NOT NULL
# ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'blogs.id' in 'where clause')

User.includes(:blogs).reference(:blogs).where('blogs.id IS NOT NULL')
#  SQL (5.8ms)  SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE `blogs`.`id` IS NOT NULL LIMIT 11
#  SQL (15.5ms)  SELECT `users`.`id` AS t0_r0, ..., `blogs`.`id` AS t1_r0, ... FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) AND `users`.`id` = 3

#=> #<ActiveRecord::Relation [#<User id: 3, ...>]>

User.includes(:blogs).where.not(blogs: { id: nil })
#  SQL (5.8ms)  SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE `blogs`.`id` IS NOT NULL LIMIT 11
#  SQL (15.5ms)  SELECT `users`.`id` AS t0_r0, ..., `blogs`.`id` AS t1_r0, ... FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) AND `users`.`id` = 3

#=> #<ActiveRecord::Relation [#<User id: 3, ...>]>

我們可以先這看到原本預設為preload 方法的includes，有時也可以適時轉換成left outer join && distinct

查詢有2篇文章以上的使用者

此為關聯搭配聚合aggregate的實際範例，這裡使用的是group搭配having

User.joins(:blogs).where('count(blogs) > 0')
#===== ActiveRecord::StatementInvalid (Mysql2::Error: Invalid use of group function)

User.joins(:blogs).group('blogs.id')
# SELECT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` GROUP BY blogs.id
#
#=> #<ActiveRecord::Relation [
  #<User id: 3, ...>, 
  #<User id: 3, ...>, 
  #<User id: 3, ...>, 
  #<User id: 3, ...]>

User.joins(:blogs).group('blogs.id').pluck(:id)
#=> [3, 3, 3, 3]

User.joins(:blogs).group('blogs.id').having('count(id) > 2')
#===== ActiveRecord::StatementInvalid (Mysql2::Error: Column 'id' in having clause is ambiguous)

#===== 錯誤: 撈不到 users，原因是 group 的地方寫錯了
User.joins(:blogs).group('blogs.id').having('count(blogs.id) > 2')
#  SELECT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` GROUP BY blogs.id HAVING (count(users.id) > 2) LIMIT 11
#=> #<ActiveRecord::Relation []>

#===== 正確: 撈到 users
User.joins(:blogs).group('users.id').having('count(blogs.id) > 2')
#  SELECT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` GROUP BY users.id HAVING (count(blogs.id) > 2) LIMIT 11
#=> #<ActiveRecord::Relation [#<User id: 3, ...>]>

查詢有2篇以上分類文章為 life 的使用者

User.joins(:blogs).group('users.id').having('count(blogs.genre = ?) > 2', Blog.genres[:life])
#  SELECT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE `users`.`banned` = FALSE GROUP BY users.id HAVING (count(blogs.genre = 0) > 2) LIMIT 11
#=> #<ActiveRecord::Relation [#<User id: 3, ...>]>

Inner Join

我們前面提到的 inner join，一共撈到了4筆資料。

User.joins(:blogs).where('blogs.id IS NOT NULL')
#   User Load (6.0ms)  SELECT `users`.* FROM `users` INNER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) LIMIT 11
#=> #<ActiveRecord::Relation [
  #<User id: 3, ...>, 
  #<User id: 3, ...>, 
  #<User id: 3, ...>, 
  #<User id: 3, ...>]>

前面我們提到，這種狀況可以用 distinct 去除多餘的資料，但沒有交代會撈到4筆的原因。資料庫一共有4篇id=3的使用者寫的文章，因此透過inner join會撈到4筆。

（詳情可以參考Inner Join）

users.id blogs.id
3 1
3 2
3 3
3 4

資料庫一共有4筆使用者id=3的部落格，因此join以後有4篇

Left (outer) join && eager load

將以上的inner join，改為left (outer) join，我們可以看到多撈了一筆id=1的使用者

User.left_joins(:blogs).where('blogs.id IS NOT NULL')
#   User Load (6.0ms)  SELECT `users`.* FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) LIMIT 11
#=> #<ActiveRecord::Relation [
  #<User id: 1, ...>,
  #<User id: 3, ...>, 
  #<User id: 3, ...>, 
  #<User id: 3, ...>, 
  #<User id: 3, ...>]>

此外left_joins也可以稱為left_outer_joins

User.left_outer_joins(:blogs).where('blogs.id IS NOT NULL')
#  User Load (6.2ms)  SELECT `users`.* FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) LIMIT 11

撈到5筆資料的原因為資料庫裡面有2個使用者，除了上述的4筆部落格對應同個使用者以外，另外一名沒有發文也會被left join到，因此會有五筆。

（詳情可以參考Left join）

users.id blogs.id
3 1
3 2
3 3
3 4
1

資料庫一共有4筆使用者id=3的部落格，加上因為left join 的關係撈到了沒有發文id=1的使用者，因此join以後有5篇

接著我們看類似的方法 ➡️ eager_load

User.eager_load(:blogs).where('blogs.id IS NOT NULL')
#  SQL (34.7ms)  SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE (blogs.id IS NOT NULL) LIMIT 11

#  SQL (15.5ms)  SELECT `users`.`id` AS t0_r0, ..., `blogs`.`id` AS t1_r0, ... FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE blogs.id IS NOT NULL AND `users`.`id` = 3

# => #<ActiveRecord::Relation [#<User id: 3, ...>]>

有別於left_joins，eager_loads 內建distinct ，因此不會找到重複的筆數。

preload & includes

跟Join不一樣，preload 為預載入資料，而另外一個 Rails 工程師比較常看到的 includes 的預設動作也是預載入。

User.preload(:blogs)
# User Load (96.0ms)  SELECT `users`.* FROM `users`
# Blog Load (28.8ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` IN (1, 2, 3, 4, 5, 1151, 1152, 1153, 1154, 1155, 1156)

User.includes(:blogs)
# User Load (4.6ms)  SELECT `users`.* FROM `users`
# Blog Load (13.1ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` IN (1, 2, 3, 4, 5, 1151, 1152, 1153, 1154, 1155, 1156)

只不過比起 preload，includes 來的還要更嫩Q

✅ 若要找使用者電子信相關鍵字含有k44的資料

User.preload(:blogs).where("users.email like '%k44%'")
#  User Load (11.7ms)  SELECT `users`.* FROM `users` WHERE (users.email like '%k44%') LIMIT 11
#  Blog Load (1.2ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` IN (2093, 2094, 2095, 2096, 2097, 2098, 2099, 2100, 2101, 2102, 2103)

❌ 若要找部落格標題含有標題關鍵字 ➡️ 錯誤原因為 users 資料表沒有title 的欄位

User.preload(:blogs).where("blogs.title like '%標題%'")
#  User Load (44.8ms)  SELECT `users`.* FROM `users` WHERE blogs.title like '%標題%' LIMIT 11
#  Traceback (most recent call last):
#  ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'blogs.title' in 'where clause')

❌ 使用includes找部落格標題含有標題關鍵字 ➡️ 錯誤原因，還是因為 users 資料表沒有title 的欄位

User.includes(:blogs).where("blogs.title like '%標題%'")
#  User Load (44.8ms)  SELECT `users`.* FROM `users` WHERE blogs.title like '%標題%' LIMIT 11
#  Traceback (most recent call last):
#  ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'blogs.title' in 'where clause')

⭐️ 補充一下，參考BigBinary文章，原本預期上述的sql statement 預期是可以執行成功，不過結果不行。

➡️ 似乎在有半sql語法的就不能這樣用，像如果我要找部落格標題等於標題1時

#==== 半 sql 語法找不到
User.includes(:blogs).where("blogs.title = '標題1'")
# ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'blogs.title' in 'where clause')

#==== 全 orm 語法找得到
User.includes(:blogs).where(blogs: {title: '標題1'})
# SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE `users`.`banned` = FALSE AND `blogs`.`title` = '標題1' LIMIT 11

#  SQL (5.8ms)  SELECT `users`.`id` AS t0_r0, ... FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE `users`.`banned` = FALSE AND `blogs`.`title` = '標題1' AND `users`.`id` = 3

✅ 使用includes找部落格標題含有標題關鍵字

➡️ 使用 preload 會失敗，因為 preload 只會預載入資料

User.preload(:blogs).references(:blogs).where("blogs.title like '%標題%'")
#  User Load (3.0ms)  SELECT `users`.* FROM `users` WHERE blogs.title like '%標題%' LIMIT 11
#  Traceback (most recent call last):
#  ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'blogs.title' in 'where clause')

➡️ 使用 includes 會成功，因為 includes 可以使用joins

User.includes(:blogs).references(:blogs).where("blogs.title like '%標題%'")
#  SQL (0.7ms)  SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE `users`.`banned` = FALSE AND blogs.title like '%標題%' LIMIT 11

#  SQL (15.5ms)  SELECT `users`.`id` AS t0_r0, ..., `blogs`.`id` AS t1_r0, ... FROM `users` LEFT OUTER JOIN `blogs` ON `blogs`.`user_id` = `users`.`id` WHERE blogs.id IS NOT NULL AND `users`.`id` = 3

Ambiguous Error

以下方的例子，看不出來id 是blogs 還是users 的，因此會跳此錯誤。

User.joins(:blogs).group('blogs.id').having('count(id) > 2')
#===== ActiveRecord::StatementInvalid (Mysql2::Error: Column 'id' in having clause is ambiguous)

通常會都是在關聯資料表之後，才可能觸發 Ambiguous Error

假設A表有B表的內容需要關聯，則A.join(“LEFT_OUTER_JOIN B on …”).select(…).where(…欲搜尋條件…)。A表跟B表有共同名字的欄位，假設叫status，若在where(…欲搜尋條件…)裡面沒有寫status是哪一個欄位，就會造成上述的錯誤。

這種錯誤發生的機率還是會有，所以必須留意。曾經遇到的狀況是有一段query寫在scope裡面，這段scope的某個撞名欄位，這欄位並沒有指定是哪個資料表而造成錯誤。

結論

這裡用了很簡單的例子介紹了很多種情境與用法，以及可能會發生的錯誤。

今天所介紹的東西，是面試中很常被拿來當作考題的情境，所以各位讀者若可以的話，今天的文章提到的觀念都要搞清楚喔！

實作上若要關聯多個資料表，可以擇優使用includes 搭配 references，因為includes & references 的用法是最實用的。

參考資料


上一篇
Day29. Rails MVC 的 Model - 與資料庫聯絡的橋樑
下一篇
Day31. Rails 搜尋的強大幫手 - Ransack
系列文
初階 Rails 工程師的養成 共 34 篇
  1. 30
    Day30. Model 與關聯 - preload, join, includes 一次釐清
  2. 31
    Day31. Rails 搜尋的強大幫手 - Ransack
  3. 32
    Day32. 使用Decorator Pattern 實作攤提
  4. 33
    Day33. 使用RSpec寫測試
  5. 34
    Day34. 結尾語 & 明年的鐵人賽
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

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