how to print pending tasks count for each entries in rails application

Question

I have generated a project, stage, task, and sub_task scaffold. each project has many stages and each stage has many tasks and each task has many sub_tasks. the corresponding table has been generated in the database. planned start and planned end date field has been added into the stage, task and sub_task table. now I want to add a column on project#index that will show the count of pending stages+tasks+sub_tasks together for each project. I have achieved this goal in project#show but I not able to do that in project#index...

projects_controller.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(project)

  <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

If only we had something like a database that's good at counting rows...

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

This does a single query and selects total_count through a subquery:

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  

I'm not even going to touch your 'SubTask' class as thats a FUBAR attempt at what should be done with a self referential association.