Rails 不踩 N+1 获取多对多中间表字段

SpiderEvgn · March 03, 2020 · Last by Qwaz1314 replied at November 07, 2020 · 9809 hits
Topic has been selected as the excellent topic by the admin.

0. 引言

分享一个关于获取多对多中间表字段的方法,关键是如何避免 N+1,接受大家指正,欢迎哪位大神有更好的办法赐教。

1. 模型关联

class User < ApplicationRecord
  has_many :topic_users
  has_many :topics, through: :topic_users
end

class TopicUser < ApplicationRecord
  belongs_to :user
  belongs_to :topic
end

class Topic < ApplicationRecord
  has_many :topic_users
  has_many :users, through: :topic_users
end

2. 查询

目的是在页面上输出 user 拥有的所有 topic 名称和分配的时间,即中间表 topic_userscreated_at

我一开始的思路是,因为要输出的是 topic 名称列表,所以就得获取到 @topics

# controller:
@topics = @user.topics

# view:
<% @topics.each do |topic| %>
  <%= topic.name %>
  <%= topic.... %>      #  想办法再获取到中间表对应的 created_at
<% end %>

然后是如何获取到中间表的字段?那无非就再通过关联查出来嘛,于是我在中间表写了个 scope 简化一下过程:

class TopicUser < ApplicationRecord
  belongs_to :user
  belongs_to :topic

  # 这里不管用 with_user 还反过来 with_topic 效果是一样的
  scope :with_user, ->(user_id) { where(user_id: user_id) }
end

# view:
<% @topics.each do |topic| %>
  <%= topic.name %>
  <%= topic.topic_users.with_user(@user.id).first.created_at %>      # 关联关系是唯一的,就直接用 first 了
<% end %>

页面输出没有问题,到此看似一切 OK。

3. N + 1 问题出现了

回过头一看控制台日志:

Topic Load (0.5ms)  SELECT "topics".* FROM "topics" INNER JOIN "topic_users" ON "topics"."id" = "topic_users"."topic_id" WHERE "topic_users"."user_id" = $1  [["user_id", 1]]

TopicUser Load (0.5ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."user_id" = $1 AND "topic_users"."topic_id" = $2 ORDER BY "topic_users"."id" ASC LIMIT $3  [["user_id", 1], ["topic_id", 1], ["LIMIT", 1]]

TopicUser Load (0.5ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."user_id" = $1 AND "topic_users"."topic_id" = $2 ORDER BY "topic_users"."id" ASC LIMIT $3  [["user_id", 1], ["topic_id", 2], ["LIMIT", 1]]

TopicUser Load (0.6ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."user_id" = $1 AND "topic_users"."topic_id" = $2 ORDER BY "topic_users"."id" ASC LIMIT $3  [["user_id", 1], ["topic_id", 3], ["LIMIT", 1]]

于是我本能地去加 includes

# controller
@topics = @user.topics.includes(:topic_users)

# console
Topic Load (0.5ms)  SELECT "topics".* FROM "topics" INNER JOIN "topic_users" ON "topics"."id" = "topic_users"."topic_id" WHERE "topic_users"."user_id" = $1  [["user_id", 1]]

TopicUser Load (0.7ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."topic_id" IN ($1, $2, $3)  [["topic_id", 1], ["topic_id", 2], ["topic_id", 3]]

TopicUser Load (0.5ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."topic_id" = $1 AND "topic_users"."user_id" = $2 ORDER BY "topic_users"."id" ASC LIMIT $3  [["topic_id", 1], ["user_id", 1], ["LIMIT", 1]]

TopicUser Load (0.6ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."topic_id" = $1 AND "topic_users"."user_id" = $2 ORDER BY "topic_users"."id" ASC LIMIT $3  [["topic_id", 2], ["user_id", 1], ["LIMIT", 1]]

TopicUser Load (0.5ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."topic_id" = $1 AND "topic_users"."user_id" = $2 ORDER BY "topic_users"."id" ASC LIMIT $3  [["topic_id", 3], ["user_id", 1], ["LIMIT", 1]]

结果却很可笑地白白增加了一条查询,颠来倒去尝试了各种查询联结方式都不行。因为中间表的字段获取是在 @topics 循环中执行的,所以无论如何都会出现这个问题。

4. 换个角度,循环中间表实例

观察 sql,因为 user 不变,我想要的无非就是一个 user_id = $1 AND topic_id IN [...] 而已,既然 @topics 无论如何都做不到,而且它的循环还是导致问题的原因,那何不直接从中间表出发试试?

于是更改如下:

# controller
@topic_users = @user.topic_users.includes(:topic)

# view:
<% @topic_users.each do |topic_user| %>
  <%= topic_user.topic.name %>
  <%= topic_user.created_at %>
<% end %>

终于,console 给出了满意的结果:

TopicUser Load (0.6ms)  SELECT "topic_users".* FROM "topic_users" WHERE "topic_users"."user_id" = $1 [["user_id", 1]]

Topic Load (0.6ms)  SELECT "topics".* FROM "topics" WHERE "topics"."id" IN ($1, $2, $3)  [["id", 1], ["id", 2], ["id", 3]]
jasl mark as excellent topic. 03 Mar 19:25

这样不行吗?

@user.topic_users.includes(topic:).map{ |tu| [tu.topic.name, tu.created_at] }

has one + include

原理上一样的,关键是循环中间表实例。

但,的确,你是对的,我少想一步,TopicUser.where(user_id: @user.id) 就等于是 @user.topic_users,放着现有 api 不用,脑子卡壳了。而且这里用不着再取 topic_id: topic_ids,直接 includes(:topic) 就行了。

更新了原文,多谢纠正!

Reply to hooopo

不明白,大佬再解释一下呗~

Reply to SpiderEvgn

topic has one user through tu

topics.includes :user

一楼的想法和我一样。应该把中间表作为查询的主表。

我注意到另一个问题,最开始@topics = @user.topics,这里查出的所有的 topic 是@user的了,那么视图中 topic.topic_users 查出来的结果自然也应该是@user的,为什么还需要 with_user(@user.id) 来再次限定。我在想去掉这个 with_user 后,配合原来的 includes 会不会还有 N+1

Reply to hooopo

usertopic 是多对多的关系,不能 has_one :user

Reply to spike76

没错,关键就在于『把中间表作为查询的主表』,在视图中循环中间表实例。

也因此,只要循环的是 topics 就避免不了这个问题,循环中 topic.topic_users 获取到的中间表 collection 也和 @user 没什么关系。

Reply to SpiderEvgn

你自己写的 关联关系是唯一的,就直接用 first 了

Reply to hooopo

关联关系是唯一的 指的是...(好吧我表述不清)

user_1 可以有 topic_1,topic_2,topic_3,但不能有两个 topic_1,反之亦然:

validates :user_id, uniqueness: { scope: :topic_id }
Reply to SpiderEvgn

😂 ,我想错模型了

幸运飞艇计划交流群 1117406

Reply to hooopo

最近也在用这个方法(select)查找中间表字段,但是发现 tables.*的字段会在 rails console 里显示,但是那个中间表字段就是不显示(虽然不影响使用,可以用 map(&:key))但是还是想能显示的话,请问有没有方法呢?

是不是这样用更好一些呢?

User.includes(topic_users: :topic)
You need to Sign in before reply, if you don't have an account, please Sign up first.