title: Rails 3 , Distinct count multiple having conditions
body: <p>Some background.  I'm using Rails 3 for a carpooling application.  I'm using fullcalendar as the jquery calendar library.  It doesn't work <em>too</em> nicely when there are lots of events for a particular day.  So, what I want to do, is to have two "eventsources".  In one, I want to return all the events, grouped by date and category, that have a count less than some value.
  For other, I want to return all the records which have a count <em>more</em> than the value.</p>
  
  <p>Apologies if this isn't clear, some sample data might make it more obvious</p>
  
  <p>Basic Record Structure:</p>
  
  <pre><code>|    id    |  date    |  category_id |
  |    1     | 1-Aug    |       1      |
  |    2     | 1-Aug    |       1      |
  |    3     | 1-Aug    |       1      |
  |    4     | 1-Aug    |       2      |
  |    5     | 1-Aug    |       2      |
  |    6     | 1-Aug    |       3      |
  </code></pre>
  
  <p>Assuming 3 is the magic count number (3 or higher should be grouped) I'd like one select to return</p>
  
  <pre><code>|    id    |  date    |  category_id |   count  |
  |    1     | 1-Aug    |       1      |     3    |
  </code></pre>
  
  <p>(I don't actually care that the ID could be combined (with MYSQL) using a group_concat), in my use case it's not important - what I want to know is that on 1st August for category 1 there are 3 entries</p>
  
  <p>The second select should return everything else</p>
  
  <pre><code>|    id    |  date    |  category_id |   count  |
  |    4     | 1-Aug    |       2      |     2    |
  |    5     | 1-Aug    |       2      |     2    |
  |    6     | 1-Aug    |       3      |     1    |
  </code></pre>
  
  <p>I don't really need the count returned, but the point is that there are individual rows for every result which doesn't have a count >= 3.</p>
  
  <p>Currently my SQL looks like this:</p>
  
  <pre><code>SELECT `pools`.*
  FROM       `pools`
  INNER JOIN `fields`    ON `fields`.`id`    = `pools`.`field_id` 
  INNER JOIN `regions`   ON `regions`.`id`   = `fields`.`region_id` 
  INNER JOIN `countries` ON `countries`.`id` = `regions`.`country_id`
  WHERE `regions`.`country_id` = 1
    AND `pools`.`confirmed`    = 1
    AND (leaving_date &gt;= '2012-04-30 00:00:00')
    AND (leaving_date &lt;= '2012-06-04 00:00:00')
  GROUP BY field_id, leaving_date
  HAVING   count(*) &gt;= 3
  ORDER BY leaving_date
  </code></pre>
  
  <p>I could iterate through the arrays returned - but I'd rather do it SQL side if it's possible.  Would also like to do it in the least number of database trips.. A general pointer would be really appreciated!  </p>