Rails find record with zero has_many records associated

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, ???

Since I discovered this question (http://stackoverflow.com/q/5319400/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. — Andrew, Mar 08 '12 at 06:33
In Rails 4, you can use the new `.not` method for the inverse. `City.includes(:photos).where.not( photos: {city_id: nil} )` — XML Slayer, Dec 01 '14 at 22:17

score 140 · Accepted Answer · edited May 23 '17 at 12:34

140

Bah, found it here: https://stackoverflow.com/a/5570221/417872

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

edited May 23 '17 at 12:34

Community

1
1

answered Mar 08 '12 at 06:31

Andrew

42,517
51
181
281

1

See also: http://stackoverflow.com/a/19080147/492465 - also answers your question about constructing the inverse and does all this with Arel – novemberkilo Sep 29 '13 at 16:06
5

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. – sixty4bit Jul 02 '15 at 01:33
8

@sixty4bit - 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. – Javid Jamae Dec 08 '15 at 18:28
1

If you’re using Rails 6.1 or higher, use where.missing to find missing relationship records. E.g.`City.where.missing(:photos)` – Conor Nov 18 '22 at 17:16

TeWu · Answer 2 · 2020-10-09T10:12:43.063

62

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

edited Oct 09 '20 at 10:12

answered Sep 09 '16 at 11:11

TeWu

5,928
2
22
36

What's the Rails 4 version of this? – fatuhoku May 19 '17 at 14:47
@fatuhoku In **Rails 4** you can either use [Yossi's answer](https://stackoverflow.com/a/23756239/6231376) (for cleaner SQL) or use `includes` (for less brittle code). In Rails 5 `left_outer_joins` is the way. – TeWu May 23 '17 at 17:08

Yossi Shasho · Answer 3 · 2015-09-20T07:55:58.397

24

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

edited Sep 20 '15 at 07:55

answered May 20 '14 at 09:42

Yossi Shasho

3,632
31
47

this is much slower compared the Andrew answer, but it works – brauliobo Oct 30 '15 at 19:48
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. – Todd Mar 23 '16 at 14:55
3

If anyone else is wondering, `LEFT OUTER JOIN` is equivalent to `LEFT JOIN` – Daniel May 01 '16 at 03:37

score 8 · Answer 4 · answered Apr 29 '15 at 22:38

8

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

answered Apr 29 '15 at 22:38

Onikoroshi

281
4
16

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)` – Mirror318 Jul 16 '20 at 21:03

score 1 · Answer 5 · answered Jun 27 '19 at 11:23

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

score 1 · Answer 6 · answered Jul 19 '19 at 16:34

1

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

answered Jul 19 '19 at 16:34

RaphaMex

2,781
1
14
30

Rails find record with zero has_many records associated

6 Answers6

Linked