Rails find record with zero has_many records associated [duplicate]
Bah, found it here: https://stackoverflow.com/a/5570221/417872
City.includes(:photos).where(photos: { city_id: nil })
In Rails versions >= 5, to find all cities that have no photos, you can use left_outer_joins
:
City.left_outer_joins(:photos).where(photos: {id: nil})
which will result in SQL like:
SELECT cities.*FROM cities LEFT OUTER JOIN photos ON photos.city_id = city.idWHERE photos.id IS NULL
Using includes
:
City.includes(:photos).where(photos: {id: nil})
will have the same result, but will result in much uglier SQL like:
SELECT cities.id AS t0_r0, cities.attr1 AS t0_r1, cities.attr2 AS t0_r2, cities.created_at AS t0_r3, cities.updated_at AS t0_r4, photos.id AS t1_r0, photos.city_id AS t1_r1, photos.attr1 AS t1_r2, photos.attr2 AS t1_r3, photos.created_at AS t1_r4, photos.updated_at AS t1_r5FROM cities LEFT OUTER JOIN photos ON photos.city_id = cities.idWHERE photos.id IS NULL
When trying to find records with no matching records from the joined table, you need to use a LEFT OUTER JOIN
scope :with_photos, joins('LEFT OUTER JOIN photos ON cities.id = photos.city_id').group('cities.id').having('count(photos.id) > 0')scope :without_photos, joins('LEFT OUTER JOIN photos ON cities.id = photos.city_id').group('cities.id').having('count(photos.id) = 0')