iT邦幫忙

2021 iThome 鐵人賽

DAY 30
0
Modern Web

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

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

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

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

class User < ApplicationRecord
  has_many :blogs
end

今天我們的主題是

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

一對一 & 一對多

在關聯式資料庫的關係裡面,一共有1對11對多多對多三種關聯,而我們先從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,我們可以使用distinctdistinctOrm的語法

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_joinseager_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)

只不過比起 preloadincludes 來的還要更嫩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

以下方的例子,看不出來idblogs 還是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

尚未有邦友留言

立即登入留言