Find all records which have a count of an association of zero and none-zero

Question

class Gallery < ApplicationRecord
  has_many :associated_images, as: :imageable
end

class Event < ApplicationRecord
  has_many :associated_images, as: :imageable
end

class Image < ApplicationRecord
  has_many :associated_images
end

class AssociatedImage < ApplicationRecord
  belongs_to :imageable, polymorphic: true
  belongs_to :image
end

I'd like to get all the images which are being used, and a different query to get all the images that are not being used (based on AssociatedImage).

I tried Image.joins(:associated_images).group('images.id').having('count(image_id) > 0') and it returns the correct result. But when I run Image.joins(:associated_images).group('images.id').having('count(image_id) = 0'), it returns an empty #<ActiveRecord::Relation []> and I'm not sure why is that.

My query is based off Find all records which have a count of an association greater than zero's discussion

score 1 · Accepted Answer · answered Apr 23 '20 at 19:30

The reason is that in SQL a count of zero happens when there are no rows. So if there are no rows, even group, there is no result.

What you want is

Image.left_joins(:associated_images).where(associated_images: {id: nil}).group('images.id')

When SQL does a left join, for an image which does not have an associated image, it fills in NULL for all the columns in the associated_images table. So the ones where the associated_images.id is nil, are the ones we want.

Find all records which have a count of an association of zero and none-zero

1 Answers1