title: How do I count the number of records that have one or more associated object?
body: <p>I have a <code>Property</code> model that <code>has_many :photos</code>. I want to count the number of <code>properties</code> that have one or more photo.</p>
  
  <p>How do I do that?</p>
  
  <p>I have tried the simple:</p>
  
  <pre><code>&gt; Property.where('properties.photos.count &gt; ?', 0).count
  
     (3.1ms)  SELECT COUNT(*) FROM "properties" WHERE (properties.photos.count &gt; 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 &gt; 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....
  </code></pre>
  
  <p>to:</p>
  
  <pre><code>&gt; Property.joins(:photos).where('photos.count &gt; ?', 0).count
  
     (3.7ms)  SELECT COUNT(*) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count &gt; 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 &gt; 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...
  </code></pre>
  
  <p>to the more advanced:</p>
  
  <pre><code>&gt;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...
  </code></pre>
  
  <p>and a few other variations, and they all produce similar errors.</p>
  
  <p>What am I doing wrong?</p>
  
  <p><strong>Note: All I want is the count of <code>properties</code> that have <code>photos.count &gt; 0</code>. 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.</strong></p>