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_users 的 created_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]]