How do I count the number of records that have one or more associated object?

Question

I have a Property model that has_many :photos. I want to count the number of properties that have one or more photo.

How do I do that?

I have tried the simple:

> Property.where('properties.photos.count > ?', 0).count

   (3.1ms)  SELECT COUNT(*) FROM "properties" WHERE (properties.photos.count > 1)
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "photos"
LINE 1: SELECT COUNT(*) FROM "properties"  WHERE (properties.photos....
                                                  ^
: SELECT COUNT(*) FROM "properties"  WHERE (properties.photos.count > 0)
from /ruby-2.3.0@myproject/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'
Caused by PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "photos"
LINE 1: SELECT COUNT(*) FROM "properties"  WHERE (properties.photos....

to:

> Property.joins(:photos).where('photos.count > ?', 0).count

   (3.7ms)  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...
                                                             ^
: SELECT COUNT(*) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
from ruby-2.3.0@myproject/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'
Caused by PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...

to the more advanced:

>Property.includes(:photos).group(['property.id', 'photos.id']).order('COUNT(photos.id) DESC').count

(0.6ms)  SELECT COUNT(DISTINCT "properties"."id") AS count_id, property.id AS property_id, photos.id AS photos_id FROM "properties" LEFT OUTER JOIN "photos" ON "photos"."property_id" = "properties"."id" GROUP BY property.id, photos.id ORDER BY COUNT(photos.id) DESC
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "property"
LINE 1: ...CT COUNT(DISTINCT "properties"."id") AS count_id, property.i...
                                                             ^
: SELECT COUNT(DISTINCT "properties"."id") AS count_id, property.id AS property_id, photos.id AS photos_id FROM "properties" LEFT OUTER JOIN "photos" ON "photos"."property_id" = "properties"."id" GROUP BY property.id, photos.id ORDER BY COUNT(photos.id) DESC
from ruby-2.3.0@myproject/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'
Caused by PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "property"
LINE 1: ...CT COUNT(DISTINCT "properties"."id") AS count_id, property.i...

and a few other variations, and they all produce similar errors.

What am I doing wrong?

Note: All I want is the count of properties that have photos.count > 0. I don't want a hash of all the properties and the count of photos. In other words, if there are 5000 properties in my db, I want to build a scope that returns just the properties that actually have photos.

Answer 1

Since all you want is the Propertys with Photos then an INNER JOIN is all you need.

Property.joins(:photos) 

That is it. If you want a scope then

class Property < ActiveRecord::Base
  scope :with_photos, -> {joins(:photos)} 
end 

To get the count using rails 3.2

Property.with_photos.count(distinct: true)  

You could also use: in rails 3.2

Property.count(joins: :photos, distinct: true) 

ActiveRecord::Calculations#count Doc

This will execute

SELECT 
  COUNT(DISTINCT properties.id) 
FROM 
  properties
  INNER JOIN photos ON photos.property_id = properties.id

Answer 2

EDIT:

Property.joins(:photos).group('photos.property_id').having('count(photos.property_id) > 1').count

#=> {1234=>2}  # 1234 is property id 2 is count of photos 

You will get the property_ids with the number of associated photos with it.

Old Answer:

You can get the properties with atleast one photos associated with it

Property.includes(:photos).where.not(photos: { property_id: nil })

As you are using rails 3.2 .not will not work you have to use

Property.includes(:photos).where("property_id IS NOT null")

Answer 3

Property.includes(:photos).where("SELECT count(photos.id) > 0 FROM photos WHERE property_id = properties.id")

As a scope:

scope :with_photos, -> { where("SELECT count(photos.id) > 0 FROM photos WHERE property_id = properties.id") }

Answer 4

You can try like this, I have done in my projects,

Photo.group(:property_id).count

You will get property id with photos count

results = { 3314=>3, 2033=>3, 3532=>2, 3565=>6, 3510=>1, 3022=>7, 648=>2, 570=>3, 4678=>3, 3540=>1, 3489=>4, 536=>1, 1715=>4 }

Answer 5

Give this a go:

class Property < ApplicationRecord
  has_many :photos

  def self.with_photos
    self.all.reject { |p| p.photos.empty? }
  end
end

Property.with_photos.count

Source

More Efficient (Rails 4+):

Property.joins(:photos).uniq.count

Source

More Efficient (Rails 5.1+):

Property.joins(:photos).distinct.count

Source

Answer 6

According to your requirement you can try this

1) A simple count of the number of properties that have 1 or more photo

To just get the number of properties which have one or more photo you can do this

Property.joins(:photos).distinct.count

As we are not using group the distinct or uniq is necessary. distinct will return ActiveRecord_Relation and uniq will return Array.

2) I would like that set of properties returned so I can create a scope of just those properties. Also, I do have lots of properties with more than 1 photo.

To get all the property objects which have one or more than one photo you can use the same query:

Property.joins(:photos).distinct

or you can use the group_by clause:

Property.joins(:photos).group('properties.id')

The difference will be that when you will use size method on the group query it will return a hash with the property_id as key and number of photos on the property as value.

Performance Update:

If you always require the count of associated object and you want to fetch it efficiently you may use counter_cache like this:

class Photo < ApplicationRecord
  belongs_to :property, counter_cache: true
end

class Property < ApplicationRecord
  has_many :photos
end

And then you will have to add a column in the properties table named photos_count and Rails will automatically update the column each time a new photo record is added or a exisisting photo record is removed. And then you can also query directly:

Property.where('photos_count > ?', 1)

This is optional and you can do this if you are facing performance issues with the data fetch.