cakephp COUNT items per month in a year

How do you use cakephp to count, for example the number of posts, made every month in a year?开发者_运维百科

Preferably using Model->find('count') and get the data in an array.

I just did something similar, using only CakePHP (no direct queries). It works in CakePHP 2, haven't tested in 1.x.

The code for your example would be something like this:

$params = array(
  'recursive' => -1,
  'fields' => array('id', 'MONTH(created)')
  'group' => array('YEAR(created)', 'MONTH(created)')
);

$numberOfPosts = $this->Model->find('count', $params);

This comes close

Query

$data = $this->Post->query("SELECT COUNT(id),MONTH(created) FROM posts GROUP BY YEAR(created), MONTH(created);");

Return

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [COUNT(id)] => 1
                    [MONTH(created)] => 3
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [COUNT(id)] => 2
                    [MONTH(created)] => 4
                )

        )

)

When using cake, I prefer to stay as close to the framework as possible. This means that I try to avoid writing queries directly in the controllers because this results in the model code being everywhere. Therefore I recommend one of two solutions

1: (and what I do with more complicated stuff): Create a view for the calculation that you want to do and create a model to match.

2: Use a query as mentioned before, but put it in the model class, not the application class.