如何打印rails应用程序中每个条目的挂起任务计数

Question

我已经生成了一个项目、阶段、任务和子任务脚手架。每个项目有许多阶段，每个阶段有许多任务，每个任务有许多子任务。已经在数据库中生成了相应的表。计划开始和计划结束日期字段已添加到阶段、任务和子任务表中。现在，我想在project#index上添加一列，它将显示每个项目的挂起阶段数+任务数+子任务数。我在#show项目中实现了这个目标，但在#index项目中无法实现。。。

项目_控制器.rb

  def index
    @projects = current_user.projects.all.order("created_at DESC").paginate(page: params[:page], per_page: 15)
  end
  def show
    @project = Project.includes(stages: {tasks: {sub_tasks: {sub_sub_tasks: :sub_three_tasks}}}).find(params[:id])
    @stages = @project.stages
    @tasks = Task.where(stage_id: @stages.ids)
    @sub_tasks = SubTask.where(task_id: @tasks.ids)

    stage_counter = 0
    task_counter = 0
    sub_task_counter = 0
    @stages.each{|s| stage_counter += 1 if s.planned_end_date.past? && s.status == 0 || s.planned_end_date.past? && s.status == 2}
    @tasks.each{|s| task_counter += 1 if s.planned_end_date.past? && s.status == 0 || s.planned_end_date.past? && s.status == 2}
    @sub_tasks.each{|s| sub_task_counter += 1 if s.planned_end_date.past? && s.status == 0 || s.planned_end_date.past? && s.status == 2}

    @count = stage_counter + task_counter + sub_task_counter
end

index.html.erb(项目(

  <table>
    <thead>
      <tr>
        <th>Project Name</th>
        <th>Activity Status</th>
      </tr>
    </thead>
    <tbody>
      <% @projects.each do |project| %>
        <tr>
          <td><%= project.project_name %></td>
          <td class="alert"><%= @total_count.to_s + " Activity Pending" %></td>
        </tr>
      <% end %>
    </tbody>
  </table>

Answer 1

如果我们有一个像数据库这样善于计算行数的东西就好了。。。

class Project < ApplicationRecord
  has_many :stages
  has_many :tasks, through: :stages
  def self.with_counts
    # subquery to fetch a count of the stages
    stages = Stage
              .where('stages.project_id = projects.id')
              .where('stages.planned_end_date < ?', Time.current)
              .select('COALESCE(COUNT(*), 0)')
              .where(status: [0,2])
    # subquery to fetch a count of the tasks
    tasks = Task
              .joins(:stage)
              .select('COALESCE(COUNT(*), 0)')
              .where(status: [0,2])
              .where('tasks.planned_end_date < ?', Time.current)
              .where('stages.project_id = projects.id')
    select(
      "projects.*",
      "(#{stages.to_sql}) + (#{tasks.to_sql}) AS total_count"
    ).group(:id)
  end
end

这执行单个查询，并通过子查询选择total_count：

SELECT projects.*,
       (SELECT COALESCE(COUNT(*), 0)
        FROM   "stages"
        WHERE  ( stages.project_id = projects.id )
               AND ( stages.planned_end_date < '2020-03-06 15:14:01.936655' )
               AND "stages"."status" IN ( 0, 2 ))
       + (SELECT COALESCE(COUNT(*), 0)
          FROM   "tasks"
                 INNER JOIN "stages"
                         ON "stages"."id" = "tasks"."stage_id"
          WHERE  "tasks"."status" IN ( 0, 2 )
                 AND ( tasks.planned_end_date < '2020-03-06 15:14:01.941389' )
                 AND ( stages.project_id = projects.id )) AS total_count
FROM   "projects"
GROUP  BY "projects"."id"
ORDER  BY "projects"."id" ASC
LIMIT  $1  

我甚至不会碰你的"SubTask"类，因为这是一个FUBAR尝试，试图用自指关联来做什么。