rails left_outer_joins count with multiple tables

Question

I am trying to make a query to get all the visits in a project and the count of each visit maintenance_reports and recognitions. For some reason, I am getting an incorrect count when I add the recognitions to the join.

@visits = @project.visits.left_outer_joins(:maintenance_reports)
      .distinct
      .select(
        'visits.*, COUNT(maintenance_reports.*) AS maintenance_reports_count'
      )
      .group('visits.id')
      .order('id asc')

I get

@visits.find(2).maintenance_reports_count => 6

    @visits = @project.visits.left_outer_joins(:maintenance_reports, :recognitions)
      .distinct
      .select(
        'visits.*, COUNT(maintenance_reports.*) AS maintenance_reports_count, COUNT(recognitions.*) AS recognitions_count'
      )
      .group('visits.id')
      .order('id asc')

@visits.find(2).maintenance_reports_count => 2

Answer 1

The counts need to be gathered as sub-queries and those sub-queries joined with the visits.

@visits = @project.visits
visit_ids = @visits.pluck(:id) # Used to limit count queries

# SQL to get MaintenanceReport and Recognition counts for these visits.
# Notice the where(visit_id: visit_ids) so we only count the relevant ones.
# This could be more DRY. Maybe a scope (:with_mr_counts) on Visit. 
mc_sql = MaintenanceReport.
  where(visit_id: visit_ids).
  group(:visit_id).
  select('visit_id, count(*) AS maintenance_report_count').
  to_sql
rc_sql = Recognition.
  where(visit_id: visit_ids).
  group(:visit_id).
  select('visit_id, count(*) AS recognition_count').
  to_sql

# Join the count queries with the visits
@visits = @visits.
  select('visits.*, maintenance_report_count, recognition_count').
  joins("LEFT JOIN (#{mc_sql}) t1 ON t1.visit_id = visits.id").
  joins("LEFT JOIN (#{rc_sql}) t2 ON t2.visit_id = visits.id").to_a