Coder Perfect

Rails find a record with a has many records value of 0 [duplicate]


This appears to be quite straightforward, but I’m having trouble finding it on Google.

If I have:

class City < ActiveRecord::Base
  has_many :photos

class Photo < ActiveRecord::Base
  belongs_to :city

I’m looking for all cities that don’t have any photos. Something like… is something I’d love to be able to call it.

City.where( photos.empty? )

…but that isn’t the case. So, how do you go about answering this type of question?

Update: Now that I’ve figured out the answer to the original query, I’m intrigued as to how you go about constructing the inverse.

For example, if I wanted to make these into scopes, I’d write:

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

Asked by Andrew

Solution #1

Unfortunately, I discovered it here:

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

Answered by Andrew

Solution #2

You may use left outer joins in Rails versions >= 5 to locate all cities without photos:

City.left_outer_joins(:photos).where(photos: {id: nil})

As a result, SQL will look like this:

SELECT cities.*
FROM cities LEFT OUTER JOIN photos ON photos.city_id =

Using includes:

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

will produce the same result, but much more uglier SQL, such as:

SELECT 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, 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 =

Answered by TeWu

Solution #3

When trying to find records from a connected table that don’t match, you’ll need to use an LEFT OUTER JOIN.

scope :with_photos, joins('LEFT OUTER JOIN photos ON = photos.city_id').group('').having('count( > 0')
scope :without_photos, joins('LEFT OUTER JOIN photos ON = photos.city_id').group('').having('count( = 0')

Answered by Yossi Shasho

Solution #4

To get all the ones containing photographs, I used a join:

joins(:photos).distinct joins(:photos).distinct joins(:photos).distinct joins(:photos).distinct joins(:photos).distinct joins(

For that particular scenario, it’s easier to write and understand. I’m not sure how much more efficient a join is than an includes, though.

Answered by Onikoroshi

Solution #5

I don’t think the accepted answer provides you exactly what you want, because you want to make an LEFT OUTER JOIN, and that solution will give you an INNER JOIN. You can use: at the very least in Rails 5.

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

In circumstances when namespacing makes the where clause cumbersome, you can use merge:

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

Answered by skepticscript

Post is based on