今天我們會用部落格跟使用者的關係來講解關聯，首先先做設定，部落格跟使用者的關係為
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對多開始破題
我們看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
第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
此為關聯搭配聚合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, ...>]>
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，一共撈到了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篇
將以上的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 ，因此不會找到重複的筆數。
跟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
以下方的例子，看不出來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 的用法是最實用的。
iThome鐵人賽
看影片追技術