您的位置: 首页  >  技术问答  >  Rails 5 - 查询冗余搜索关联

Rails 5 - 查询冗余搜索关联

分类: 技术问答 • 2022-08-06 11:04:06
问题描述：

我正在使用数据表显示4列。Rails 5 - 查询冗余搜索关联

名称| Count |最近交货日期|最远期限。

我终于有表格显示（我相信）基于测试的正确数据，但它现在需要很长的时间来查询，我相信这只是由于我的黑客 - 斜线方法这行得通。查询从事务点看起来干净/适当，但有很多。我相信我应该利用.join和.pluck，但我不确定正确的方法。

附加是我创建数据的函数。 （在初始化我送一个clients_resource那就是-all-活跃客户。我已经添加了简洁在线评论的用户的列表。）

def data 
    clients.map do |client| 
     [ 
     ## The output is a link to the client, raw industry field, the number of 'tasks' they currently have 
     ## as noted in ##TASK_COUNT## 
     ## Then I want to link to the nearest TASK due date. (task_end_date) 
     ## This happened in ##TASK_END_DATE## 
     ## The final one is similar but finds the end_date withinj the Project 
     ## This is in ##PROJECT_END_DATE## 
     link_to(client.name, client), 
     client.industry, 
     client.status, 

     ##TASK_COUNT## 
     if client.tasks.present? ## My purpose here is to avoid 500 errors if a client has no tasks. Seemed like a hack 
      task = client.tasks.order(:task_start_date).first 
      project = Project.find(task.project_id) 
      link_to(client.tasks.count, url_helpers.project_tasks_path(project)) 
     else 
      client.tasks.count 
     end, 
     ##TASK_END_DATE## 
     if client.tasks.present? ## Again, hack method 
      task = client.tasks.order('task_end_date desc').first ## Exactly as it says - Was only way I could get the '' 
                  ## Typing this out I realize this logic is incorrect. It should return the NEAREST future task_end_date, but it is returning the furthest task_end_date, perior 
      project = task.project ## Was a hack to try to reduce the queries since the relationship will allow this query to work 
      #project = client.projects.order('end_date desc').first 
      if task.task_end_date.future? ## Hack to ensure the task_end_date is in the future other-wise it should print a warning 
      link_to(task.task_end_date.strftime("%B %e, %Y"), url_helpers.project_task_path(project, task)) 

      else 
       link_to("Is this project late?", url_helpers.project_tasks_path(client.nearest_project_id)) 
      end 
     else 
      'No Projects' 
     end, 
      ##PROJECT_END_DATE## 
     if client.tasks.present? && client.nearest_project_start.present? ##Hack to ensure it has tasks, and that the nearest_project_start has content. 
      project = Project.find(client.furthest_project_id) ## Gets the ProjectID for the furthest_project_id - This is handled by the model on save to help alliviate queries 
      link_to(project.end_date.strftime("%B %e, %Y"), url_helpers.project_task_path(project, client.furthest_project_id)) 
     else 
      'No Tasks Pending' 
     end 
     ] 
    end 
    end

同样，这种感觉令人难以置信错，但我不确定如何更好地把它。

我还附上3个模型ERD的一个片段，以防万一。

流量是

客户端 - >项目 - >任务，而是往回走也。 （所以Task.first.client将返回客户端）。

我想弄清楚如何使这些查询更干净，也更准确。只是做原始的SQL我去？我用SQL比使用方法更好。

控制台的查询

User Load (1.8ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2 [["id", 1], ["LIMIT", 1]] 
    (1.8ms) SELECT COUNT(*) FROM "clients" WHERE "clients"."user_id" = $1 AND (status != 'Not Active') AND (status != 'Suspended') [["user_id", 1]] 
    CACHE (0.4ms) SELECT COUNT(*) FROM "clients" WHERE "clients"."user_id" = $1 AND (status != 'Not Active') AND (status != 'Suspended') [["user_id", 1]] 
    Client Load (2.1ms) SELECT "clients".* FROM "clients" WHERE "clients"."user_id" = $1 AND (status != 'Not Active') AND (status != 'Suspended') ORDER BY name asc LIMIT $2 OFFSET $3 [["user_id", 1], ["LIMIT", 30], ["OFFSET", 0]] 
    Project Load (5.8ms) SELECT "projects".* FROM "projects" WHERE "projects"."client_id" IN (2, 1, 4, 3) 
    Task Load (2.1ms) SELECT "tasks".* FROM "tasks" WHERE "tasks"."project_id" IN (5, 6, 3, 4, 1, 7, 2) 
    Task Load (1.9ms) SELECT "tasks".* FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 ORDER BY "tasks"."task_start_date" ASC LIMIT $2 [["client_id", 2], ["LIMIT", 1]] 
    Project Load (1.4ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]] 
    (1.8ms) SELECT COUNT(*) FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 [["client_id", 2]] 
    Task Load (4.1ms) SELECT "tasks".* FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 ORDER BY task_end_date asc LIMIT $2 [["client_id", 2], ["LIMIT", 1]] 
    CACHE Project Load (0.4ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]] 
    CACHE Project Load (0.5ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]] 
    Task Load (2.0ms) SELECT "tasks".* FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 ORDER BY "tasks"."task_start_date" ASC LIMIT $2 [["client_id", 1], ["LIMIT", 1]] 
    Project Load (1.5ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]] 
    (1.8ms) SELECT COUNT(*) FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 [["client_id", 1]] 
    Task Load (3.2ms) SELECT "tasks".* FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 ORDER BY task_end_date asc LIMIT $2 [["client_id", 1], ["LIMIT", 1]] 
    CACHE Project Load (0.3ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]] 
    CACHE Project Load (0.3ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]] 
    (2.0ms) SELECT COUNT(*) FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 [["client_id", 4]] 
Started GET "/clients" for 73.106.189.182 at 2017-10-15 01:18:19 +0000 
    Task Load (29.1ms) SELECT "tasks".* FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 ORDER BY "tasks"."task_start_date" ASC LIMIT $2 [["client_id", 3], ["LIMIT", 1]] 
Cannot render console from 73.106.189.182! Allowed networks: 127.0.0.1, ::1, 127.0.0.0/127.255.255.255 
Processing by ClientsController#index as HTML 
    Project Load (45.9ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]] 
    (11.1ms) SELECT COUNT(*) FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 [["client_id", 3]] 
    User Load (5.8ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2 [["id", 1], ["LIMIT", 1]] 
    Task Load (100.6ms) SELECT "tasks".* FROM "tasks" INNER JOIN "projects" ON "tasks"."project_id" = "projects"."id" WHERE "projects"."client_id" = $1 ORDER BY task_end_date asc LIMIT $2 [["client_id", 3], ["LIMIT", 1]] 
    Rendering clients/index.html.erb within layouts/application 
    CACHE Project Load (0.5ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]] 
    Rendered clients/index.html.erb within layouts/application (9.6ms) 
    CACHE Project Load (0.4ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]]

从本质上讲，这些〜20行是我所关心的过程中一个片段，加载时间也难以承受甚至刺。在我开始细化这些查询之前，这并不是那么广泛。

欢迎任何输入 - 我不确定附加的其他代码库。

答

为了加快速度并避免那些“n + 1”查询，您可以使用急切加载（包括），而不是每次使用计数器缓存计算所有关联记录。以下资源描述了这些技术：

Eager Loading Associations

ActiveRecord Associations Counter Cache (4.1.2.3)

相关推荐