Need help with MySQL query

Question

Hi,

I need help with MySQL query. Here is a part of my database:

+---------------+
| users         |
+---------------+
| id            |---
| username      |  |    +-----------------+     +---------------------+
| first_name    |  |    |users_in_projects|     | regular_posts       |
| last_name     |  |    +-----------------+     +---------------------+
| email         |  |    | id              |--   | id                  |
| password      |  |    | project_id      | |   | date                |
| active        |  |----| user_id         | |   | size                |
| created       |       +-----------------+ |---| users_in_project_id |
| modified      |                               +---------------------+
| group_id      |
+---------------+

and the associations:

User hasMany UsersInProject
UsersInProject belongsTo User
UsersInProject hasMany RegularPost
RegularPost belongsTo UsersInProject

What I want is the result table with:

username and summary size of regular posts in every month for example year 2010, something like this:

username 2010-01 2010-02 2010-03 2010-04 2010-05 2010-06 2010-07 2010-08 2010-09 2010-10 2010-11 2010-12
foo        0.75    0.75    1        1      1       0.5    0.75    0.75     0.75    0.75    0.75   0.75
bar         0.5     0.5   0.5      0.5     1       0.75   0.75      1        1       1       1      1
...

or array like:

Result => array (
        [0] => array (
                user.id => 1
                user.username => foo
                RegularPost => array (
                        [0] => array (
                                date => 2010-01
                                size => 0.75
                        )
                        [1] => array (
                                date => 2010-02
                                size => 0.75
                        )
                        ...
                )
        )
        [1] => array (
                user.id => 2
                user.username => bar
                RegularPost => array (
                        [0] => array (
                                date => 2010-01
                                size => 0.5
                        )
                        [1] => array (
                                date => 2010-02
                                size => 0.5
                        )
                        ...
                )
        )
        ...
)

I can write the query for one user, but for every don't.

$results = $this->User->UsersInProject->RegularPost->find('all', array(
            'recursive' => 0,
            'fields'=> array(
                        'RegularPost.id',
                        'RegularPost.date',
                        'SUM(RegularPost.size) AS size',
                ),
            'conditions' => array(
                'UsersInProject.user_id' => $id,
                'RegularPost.date like' => '2010%',
            ),
            'group' => array('RegularPost.date')
        ));

SELECT
        `RegularPost`.`date`,
        SUM(`RegularPost`.`size`) AS size
FROM
        `regular_posts` AS `RegularPost`
LEFT JOIN
        `users_in_projects` AS `UsersInProject`
ON
        (`RegularPost`.`users_in_project_id` = `UsersInProject`.`id`)
WHERE
        `UsersInProject`.`user_id` = 1 AND `RegularPost`.`date` like '2010%'
GROUP BY
        `RegularPost`.`date`;

And the result:

+------------+---------+
| date       | size    |
+------------+---------+
| 2010-01-01 | 0.75000 |
| 2010-02-01 | 0.75000 |
| 2010-03-01 | 0.75000 |
| 2010-04-01 | 0.75000 |
| 2010-05-01 | 0.75000 |
| 2010-06-01 | 1.00000 |
| 2010-07-01 | 0.75000 |
| 2010-08-01 | 0.75000 |
| 2010-09-01 | 1.00000 |
| 2010-10-01 | 0.75000 |
| 2010-11-01 | 0.75000 |
| 2010-12-01 | 1.00000 |
+------------+---------+

Please help.

Answer 1

Have you tried this... removing the user id from the where clause and adding it into the group by clause?

SELECT
        `RegularPost`.`date`,
        SUM(`RegularPost`.`size`) AS size
FROM
        `regular_posts` AS `RegularPost`
LEFT JOIN
        `users_in_projects` AS `UsersInProject`
ON
        (`RegularPost`.`users_in_project_id` = `UsersInProject`.`id`)
WHERE
        `RegularPost`.`date` like '2010%'
GROUP BY
        `UsersInProject`.`user_id`, `RegularPost`.`date`;