ActiveRecord - find records that its association count is 0

Question

In my Ruby on Rails app I have the following model:

class SlideGroup < ApplicationRecord
  has_many :survey_group_lists, foreign_key: 'group_id'
  has_many :surveys, through: :survey_group_lists
end

I want to find all orphaned slide groups. Orphaned slide group is slide group which is not connected to any survey. I've been trying following query but it does not return anything and I'm sure that I have orphaned records in my test database:

SlideGroup.joins(:surveys).group("slide_groups.id, surveys.id").having("count(surveys.id) = ?",0)

this generates following sql query:

SlideGroup Load (9.3ms)  SELECT "slide_groups".* FROM "slide_groups" INNER JOIN "survey_group_lists" ON "survey_group_lists"."group_id" = "slide_groups"."id" INNER JOIN "surveys" ON "surveys"."id" = "survey_group_lists"."survey_id" GROUP BY slide_groups.id, surveys.id HAVING (count(surveys.id) = 0)

Answer 1

You're using joins, which is INNER JOIN, whereas what you need is an OUTER JOIN - includes:

SlideGroup.includes(:surveys).group("slide_groups.id, surveys.id").having("count(surveys.id) = ?",0)

A bit cleaner query:

SlideGroup.includes(:surveys).where(surveys: { id: nil })