cakephp COUNT items per month in a year

Question

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.

Answer 1

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
                )

        )

)

Answer 2

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.