按孩子的属性排序时n + 1问题

The following query suffers from the 'n+1' problem of loading each order for each record:

以下查询遇到加载每条记录的每个订单的“n + 1”问题:

Job.joins('LEFT JOIN orders ON orders.job_id = jobs.id').order("orders.featured")

Same for this:

同样如此:

Job.includes(:order).order("orders.featured")

Removing the .order(...) part removes the n + 1 issue, but then it's not ordered. Any ideas how to fix this? Do I need to create a column in the parent for the 'featured' attribute?

删除.order(...)部分会删除n + 1问题,但之后它不会被排序。任何想法如何解决这一问题?我是否需要在父级中为'featured'属性创建一个列?

Output:

  Order Load (0.4ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 26]]
  Rendered jobs/_job.html.erb (1.9ms)
  Order Load (0.3ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 3]]
  Rendered jobs/_job.html.erb (2.0ms)
  Order Load (0.3ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 52]]
  Rendered jobs/_job.html.erb (1.7ms)
  Order Load (0.3ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 13]]
  Rendered jobs/_job.html.erb (1.9ms)
  Order Load (0.3ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 34]]
  Rendered jobs/_job.html.erb (1.9ms)
  Order Load (0.4ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 64]]
  Rendered jobs/_job.html.erb (2.8ms)
  Order Load (0.4ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 94]]
  Rendered jobs/_job.html.erb (3.2ms)
  Order Load (0.4ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 60]]
  Rendered jobs/_job.html.erb (3.1ms)
  Order Load (0.4ms)  SELECT  "orders".* FROM "orders" WHERE "orders"."job_id" = $1 LIMIT 1  [["job_id", 29]]

2 个解决方案

#1

Try using preload for the associated data:

尝试将preload用于关联数据:

Job.joins('LEFT JOIN orders ON orders.job_id = jobs.id')
  .order("orders.featured")
  .preload(:orders)

#2

Job.includes(:order).order("orders.featured")

Includes doesn't join the tables together until you call it in the view. It will actually do 2 queries. If you want the 2 tables to be joined to do the order, you need to use eager_load:

在您在视图中调用它之前,包含不会将表连接在一起。它实际上会做2个查询。如果要连接2个表来执行订单,则需要使用eager_load:

Job.eager_load(:order).order("orders.featured")

http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html

#1