cakephp 2.x pivot table

Question

I have a table as follows

Tablename : tb_daily_call_reports

+------------+-----------+
| username   |  date     |
+------------+-----------+
| Peter      |  5/5/2014 |
+------------+-----------+
| Steve      |  5/5/2014 | 
+------------+-----------+
| Peter      |  5/5/2014 |
+------------+-----------+
| Peter      |  5/5/2014 |
+------------+-----------+
| Peter      |  5/6/2014 |
+------------+-----------+
| Steve      |  5/6/2014 |
+------------+-----------+
| Peter      |  5/6/2014 |
+------------+-----------+
| James      |  5/7/2014 |
+------------+-----------+
| Steve      |  5/7/2014 |
+------------+-----------+
| James      |  5/8/2014 |
+------------+-----------+
| Peter      |  5/8/2014 | 
+------------+-----------+
| Steve      |  5/8/2014 |
+------------+-----------+
| James      |  5/8/2014 |
+------------+-----------+
| James      |  5/8/2014 |
+------------+-----------+

What I would like to do is count the users in particular day and display the output as follows

+-----------+--------+---------+---------+
|           | Peter  | Steve   |  James  |
+-----------+--------+---------+---------+
| 5/5/2014  |   3    |   1     |    0    |
+-----------+--------+---------+---------+
| 5/6/2014  |   2    |   1     |    0    |
+-----------+--------+---------+---------+
| 5/7/2014  |   0    |   1     |    1    |
+-----------+--------+---------+---------+
| 5/8/2014  |   1    |   1     |    3    |
+-----------+--------+---------+---------+

Using SQL query I get the required result (for clarity username = user_detail_id)

SELECT date,
count(IF (user_detail_id = 'Peter', date, NULL)) AS 'Peter',
count(IF (user_detail_id = 'Steve', date, NULL)) AS 'Steve',
count(IF (user_detail_id = 'James', date, NULL)) AS 'James',
FROM tb_daily_call_reports 
GROUP BY  date;

I need to implement this using CakePHP 2.x.

Thank you!

Answer 1

I suppose you are in your DailyCallReportsController

// First I search for all the distinct usernames that I have in my table

$names = $this->DailyCallReport->find(
    'all',
    array(
        'fields' => array('DISTINCT DailyCallReport.user_detail_id'),
        'recursive' => -1
    )
);


// Then for every username I create a virtual field for my Model

$fields = array('date');

foreach($names as $record)
{
    $user_detail_id  = $record['DailyCallReport']['user_detail_id '];
    $this->DailyCallReport->virtualFields['user_'.$user_detail_id] = "COUNT(IF (user_detail_id = $user_detail_id, date, NULL))";
    $fields[] = 'user_'.$user_detail_id;
}

// Finally I do the query

$pivot = $this->DailyCallReport->find(
    'all',
    array(
        'fields' => $fields,
        'recursive' => -1,
        'group' => array('date')
    )
);