How do I find out how many records of 1 model have associated objects of an association?

Question

I have two models - Property & Photos.

class Property < ActiveRecord::Base
  has_many :photos, dependent: :destroy
end

class Photo < ActiveRecord::Base
  belongs_to :property
end

All I want to do is create a scope that returns only the Properties that actually have photos (i.e. photos.count > 0).

I have tried a million iterations of queries, and they don't work for one reason or another.

See some examples of what I have tried and the results:

[32] pry(main)> Property.includes(:photos).where('photos.count > 0').count
   (4.1ms)  SELECT COUNT(DISTINCT "properties"."id") FROM "properties" LEFT OUTER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...
                                                             ^
[33] pry(main)> Property.joins(:photos).where('photos.count > 0').count
   (11.1ms)  SELECT COUNT(*) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...
                                                             ^

[38] pry(main)> Property.joins(:photos).count("properties.id").count
   (158.2ms)  SELECT COUNT(properties.id) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"
NoMethodError: undefined method `count' for 72604:Fixnum
from (pry):38:in `__pry__'

[39] pry(main)> Property.joins(:photos).count("properties.id")
   (50.6ms)  SELECT COUNT(properties.id) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"
=> 72604

[40] pry(main)> Photo.joins(:properties).count("photos.id")
ActiveRecord::ConfigurationError: Association named 'properties' was not found on Photo; perhaps you misspelled it?
from /.rvm/gems/ruby-2.3.7@myapp/gems/activerecord-3.2.22.5/lib/active_record/associations/join_dependency.rb:112:in `build'

[41] pry(main)> Photo.joins(:property).count("photos.id")
   (65.0ms)  SELECT COUNT(photos.id) FROM "photos" INNER JOIN "properties" ON "properties"."id" = "photos"."property_id"
=> 72604

[42] pry(main)> Photo.joins(:property).count("photos.id")
   (60.5ms)  SELECT COUNT(photos.id) FROM "photos" INNER JOIN "properties" ON "properties"."id" = "photos"."property_id"
=> 72604

[43] pry(main)> Property.joins(:photos).count("properties.id").distinct
   (46.4ms)  SELECT COUNT(properties.id) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"
NoMethodError: undefined method `distinct' for 72604:Fixnum
from (pry):43:in `__pry__'

[44] pry(main)> Property.joins(:photos).distinct.count("properties.id")
=> 0

This should be simple, but for some reason it's much more difficult than I expected.

Thoughts?

Answer 1

In Rails-5, you can use left_outer_joins,

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

SQL generated will be,

SELECT properties.*
FROM properties 
LEFT OUTER JOIN photos 
ON photos.property_id = property.id
WHERE photos.id IS NULL

In Rails-3, You can try following

Property.joins("LEFT OUTER JOIN photos ON properties.id = photos.property_id").where(photos: {id: nil})

Answer 2

Using Property.joins(:photos) (which performs an inner join) will return the properties that have related photos. Meaning photos that have a property_id. While the answer using left_outer_joins, LEFT JOIN or LEFT OUTER JOIN will work, it's using the left join incorrectly by having the .where(photos: { id: nil }). INNER JOIN, JOIN, or joins between Property and Photo will result in what I have stated at the beginning of this answer. The left joins will return all properties with or without related photos.

Answer 3

In order to get only the Properties that actually have photos (i.e. photos.count > 0), you need to perform inner join of two tables.

In rails 5, you can do it by below command

Property.joins(:photos)

This will be transformed to SQL as

SELECT  "properties".* FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"

If you want to add more conditions on your queries, you can add Where clause

Property.joins(:photos).where(properties: {id: [1, 2]})