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对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: '[email protected]', price: 100)

#==== incorrect
order.invoices.create!(customer_name: 'han', customer_phone: '0912559059', customer_email: '[email protected]', 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 的用法是最实用的。

参考资料

<<: SQL 语言和你 SAY HELLO!!

>>: Day 15 : 笔记篇 02 — 使用「渐进式总结」来写笔记，逐步萃取出高含金量的知识内容

Day30. Model 与关联 - preload, join, includes 一次厘清

一对一 & 一对多

user.blogs

blog.user

关联资料表查询

查询有文章的使用者

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

查询有2篇以上分类文章为 life 的使用者

Inner Join

Left (outer) join && eager load

preload & includes

Ambiguous Error

结论

参考资料

第三十天：为 TeamCity 设计的 Kotlin DSL

[Day29] 实战 - 三关价偏多且 KDJ 维持多头排列

Day 0x4 - 请求 API 前的前置动作(Part 1)[Nonce, Hash Id, Sign]

月费如何定价？免费试用会提高订阅率吗？

Day 24 CSS3 < 过渡 transition>

EP16 - 用生活化的例子解释容器，是否搞错了些什麽

画一个三角形（上）

30天学会C语言: Day 13-递回体验镇魂曲

Day17

【Day25】Git 版本控制 - SHA-1 是什麽