Search code examples
coldfusioncfwheels

How to count records by grouping them in cfWheels?

I have a case where I need to count the number of records grouped by publishing year. I've looked at the documentation, and the net in general, but I can't find what to use.

e.g.

  • 2013 = 100 books published
  • 2012 = 95 books
  • etc..

Using Oracle SQL, this is done using:

select date_published, count(*) 
from   publications 
group by date_published 
order by date_published desc

I'm just wondering how to translate this to CFWheels.

Solution

  • Try this:

    publications=model("publication").findAll( 
     select="date_published, COUNT(date_published) AS publishCount"
    , group="date_published"
    , order="date_published DESC" );

    NB, COUNT() is a case-sensitive command in wheels.

    PS, or you can do what matt says - you could even attach it to the model so you could do publications.getPubCountByYear() etc.