Problem
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
end
class Photo < ActiveRecord::Base
belongs_to :city
end
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: https://stackoverflow.com/a/5570221/417872
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 = city.id
WHERE photos.id IS NULL
Using includes:
City.includes(:photos).where(photos: {id: nil})
will produce the same result, but much more uglier SQL, such as:
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
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 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')
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 https://stackoverflow.com/questions/9613717/rails-find-record-with-zero-has-many-records-associated