title: How do I query the number of objects on an associated model?
body: <p>I have a <code>Profile</code> and that <code>has_many :ratings</code>.</p>
  
  <p>What I want to find are the number of <code>Profile</code> objects that have more than 1 rating record associated with it. </p>
  
  <p>I tried the following to no avail:</p>
  
  <pre><code>&gt; Profile.includes(:ratings).where('ratings.count &gt; 0').count
     (38.2ms)  SELECT COUNT(*) FROM "profiles" WHERE (ratings.count &gt; 0)
  ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "ratings"
  LINE 1: SELECT COUNT(*) FROM "profiles" WHERE (ratings.count &gt; 0)
                                                 ^
  : SELECT COUNT(*) FROM "profiles" WHERE (ratings.count &gt; 0)
  </code></pre>
  
  <p>And</p>
  
  <pre><code>&gt; Profile.where('ratings.count &gt; 1').count
     (28.1ms)  SELECT COUNT(*) FROM "profiles" WHERE (ratings.count &gt; 1)
  ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "ratings"
  LINE 1: SELECT COUNT(*) FROM "profiles" WHERE (ratings.count &gt; 1)
                                                 ^
  : SELECT COUNT(*) FROM "profiles" WHERE (ratings.count &gt; 1)
  </code></pre>
  
  <p><strong>Note</strong> That my <code>ratings</code> model does not include a column called <code>count</code>. What I am trying to do is <code>count</code> the number of <code>ratings</code> objects associated with each <code>profile</code> record and return the number of <code>Profile</code> records that have more than <code>1</code> rating associated object.</p>
  
  <p>How do I achieve that with ActiveRecord?</p>
  
  <p><strong>Edit 1</strong></p>
  
  <p>Trying two more queries per user793789's suggestions:</p>
  
  <pre><code>&gt; Profile.includes(:ratings).where('ratings.count &gt; 1').references(:ratings).count
     (55.3ms)  SELECT COUNT(DISTINCT "profiles"."id") FROM "profiles" LEFT OUTER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count &gt; 1)
  ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
  LINE 1: ...N "ratings"."profile_id" = "profiles"."id" WHERE (ratings.co...
                                                               ^
  : SELECT COUNT(DISTINCT "profiles"."id") FROM "profiles" LEFT OUTER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count &gt; 1)
  
  &gt; Profile.joins(:ratings).where('ratings.count &gt; 1').count
     (40.4ms)  SELECT COUNT(*) FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count &gt; 1)
  ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
  LINE 1: ...N "ratings"."profile_id" = "profiles"."id" WHERE (ratings.co...
                                                               ^
  : SELECT COUNT(*) FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count &gt; 1)
  </code></pre>