Rails find record with zero has_many records associated [duplicate]

Question

This seems fairly simple but I can't get it to turn up on Google.

If I have:

class City < ActiveRecord::Base
  has_many :photos
end

class Photo < ActiveRecord::Base
  belongs_to :city
end

I want to find all cities that have no photos. I'd love to be able to call something like...

City.where( photos.empty? )

...but that doesn't exist. So, how do you do this kind of query?

---

Update: Having now found an answer to the original question, I'm curious, how do you construct the inverse?

IE: if I wanted to create these as scopes:

scope :without_photos, includes(:photos).where( :photos => {:city_id=>nil} )
scope :with_photos, ???

Bergstrom

Since I discovered this question (https://mcmap.net/q/117565/-want-to-find-records-with-no-associated-records-in-rails/417872) I guess this can be closed. It's probably worthwhile to have one more way to find this in google though, this kind of thing is hard to describe and therefore hard to search for. – Bergstrom

In Rails 4, you can use the new .not method for the inverse. City.includes(:photos).where.not( photos: {city_id: nil} ) – Stipendiary

Answer 1

Bah, found it here: https://mcmap.net/q/117565/-want-to-find-records-with-no-associated-records-in-rails

City.includes(:photos).where(photos: { city_id: nil })

Bergstrom

See also: https://mcmap.net/q/117565/-want-to-find-records-with-no-associated-records-in-rails - also answers your question about constructing the inverse and does all this with Arel – Luker

I don't understand how this is correct? Isn't it looking for photos that don't have a city_id? That's not the same as cities for which there is no photo with that particular city's id as the foreign key. – Stableboy

@Stableboy - It works because when you do includes it does a join. In a SQL join, you get all the fields of both tables (in this case cities and photos) for each row unless you change the projection of the query. So, he's using that to his advantage to check whether a required database identifier is present. If it's not, then there was no record on the photos side of the join. You could also use photos: {id: nil} if that is more clear. – Languorous

If you’re using Rails 6.1 or higher, use where.missing to find missing relationship records. E.g.City.where.missing(:photos) – Landrum

Answer 2

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.id
WHERE 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_r5
FROM cities LEFT OUTER JOIN photos ON photos.city_id = cities.id
WHERE photos.id IS NULL

Zanazander

What's the Rails 4 version of this? – Strom

@Strom In Rails 4 you can either use Yossi's answer (for cleaner SQL) or use includes (for less brittle code). In Rails 5 left_outer_joins is the way. – Zanazander

Answer 3

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')

Outhe

this is much slower compared the Andrew answer, but it works – Yours

Actually I would say this is less complicated - this answer demonstrates the actual technique used, or the actual DB query that will be needed. The accepted answer essentially obfuscates this. – Inamorata

If anyone else is wondering, LEFT OUTER JOIN is equivalent to LEFT JOIN – Redan

Answer 4

I used a join to get all the ones with photos:

scope :with_photos, -> { joins(:photos).distinct }

Easier to write and understand, for that particular case. I'm not sure what the efficiency is of doing a join vs doing an includes, though

Vickievicksburg

adding to this way of doing it, to get all records that don't have an association: City.where.not(id: City.joins(:photos).distinct) – Warrior

Answer 5

I don't believe the accepted answer gives you exactly what you're looking for, as you want to do a LEFT OUTER JOIN and that answer will give you a INNER JOIN. At least in Rails 5 you can use:

scope :without_photos, left_joins(:photos).where( photos: {id: nil} )

or you can use merge in cases where namespacing will make the where clause cumbersome:

scope :without_photos, left_joins(:photos).merge( Photos.where(id: nil) )

Stopgap

Answer 6

If you are not running Rails 5+ and performance is a must-have, avoid useless ActiveRecord creation and get just what you need:

City.where("NOT EXISTS(SELECT 1 FROM photos WHERE photos.city_id = cities.id LIMIT 1)")

Venezuela