主页 / 问题 / 1550336
Accepted
Vyacheslav Odinokov
Asked:2023-11-08 23:02:40 +0000 UTC2023-11-08 23:02:40 +0000 UTC

优化 - Rails own_to 有一个 n+1 问题，如何使其在 sql 中具有 join？

  • 772

我有日志记录，它们有指向用户的链接。

日志模型

class Syslog < ApplicationRecord
 belongs_to :user
end

用户模型没有什么特别的，我没有在那里添加 has_many 。

现在当我执行 Syslog.all 时

在视图中我向用户讲话：

    <% @syslogs.each do |syslog| %>
      <tr>
        <td><%= syslog.datetime %></td>
        <td><%= syslog.user.username %></td>
      </tr>
    <% end %>

在日志中我看到正在发出许多重复的请求。

  Syslog Load (1.2ms)  SELECT `SYSLOG`.* FROM `SYSLOG` INNER JOIN `USERS` ON `USERS`.`I_ID` = `SYSLOG`.`I_UID` WHERE (I_ELCAT <= '3') LIMIT 50
  ↳ app/views/syslogs/index.html.erb:13
  User Load (1.4ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (1.1ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 2 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (1.3ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -20 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (2.2ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (1.2ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -11 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -11 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (1.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -17 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -11 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.1ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -1 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (2.1ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -15 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -20 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (1.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -12 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (1.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -13 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -13 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -13 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -13 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -13 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  CACHE User Load (0.0ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -13 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  User Load (0.8ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = -18 LIMIT 1
  ↳ app/views/syslogs/index.html.erb:16
  Rendered syslogs/index.html.erb within layouts/application (Duration: 163.4ms | Allocations: 64237)
  User Load (1.3ms)  SELECT `USERS`.* FROM `USERS` WHERE `USERS`.`I_ID` = 11 LIMIT 1

事实证明，从技术上讲，首先选择所有日志。然后在循环中为每个日志选择一个用户。

我需要通过一个或最少数量的请求来完成此操作。如何实现这一目标？

sql

1 个回答

  1. Best Answer

    这不需要通过 join 来完成；ActiveRecord 为此有不同的预加载选项

    @syslogs = Syslog.includes(:user).all
    • 1

相关问题