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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…