I have a StatsController with which I'd like to make a summary of all logins and all downloads for each month.
class Login extends AppModel { public $belongsTo = array('User'); public $hasMany = array('Download'); } class Download extends AppModel { public $belongsTo = array( 'ItemFile', 'User', 'Login' ); } I can easily create separate queries but would need to merge them. i haven't been able to figure that part out. public function admin_summary() { $logins = $this->Login->find( 'all', array( 'fields' => array( 'MONTHNAME(created) AS month', 'COUNT(*) AS num_logins' ), 'recursive' => -1, 'group' => array('month') ) ); $downloads = $this->Download->find( 'all', array( 'fields' => array( 'MONTHNAME(created) AS month', 'COUNT(*) AS num_downloads' ), 'recursive' => -1, 'group' => array('month') ) ); // ... } This gives me: Array ( [0] => Array ( [Login] => Array ( [month] => July [num_logins] => 16 ) ) [1] => Array ( [Login] => Array ( [month] => June [num_logins] => 168 ) ) [2] => Array ( [Login] => Array ( [month] => May [num_logins] => 64 ) ) ) app/controllers/stats_controller.php (line 171) Array ( [0] => Array ( [Download] => Array ( [month] => July [num_downloads] => 11 ) ) [1] => Array ( [Download] => Array ( [month] => June [num_downloads] => 367 ) ) [2] => Array ( [Download] => Array ( [month] => May [num_downloads] => 15 ) ) ) What I'm after is something like: Array ( [0] => Array ( 'July' => array( 'Download' => Array ( 'num_downloads' => 11 ), 'Login' => Array ( 'num_logins' => 16 ) ) ) ... ) or even ... Array ( [0] => Array ( 'July' => array( 'Download' => 11, 'Login' => 16 ) ) ... ) Incidentally, I've been tryng to figure out a way to do this in a single query (MySQL) but I get strange results: mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM logins GROUP BY month; +-------+------------+ | month | num_logins | +-------+------------+ | July | 15 | | June | 168 | | May | 64 | +-------+------------+ 3 rows in set (0.00 sec) mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads FROM downloads GROUP BY month; +-------+---------------+ | month | num_downloads | +-------+---------------+ | July | 11 | | June | 367 | | May | 15 | +-------+---------------+ 3 rows in set (0.00 sec) mysql> SELECT MONTHNAME(l.created) AS month, COUNT(l.id) AS num_logins, COUNT(d.id) AS num_downloads FROM logins AS l LEFT JOIN downloads AS d ON d.login_id = l.id GROUP BY month; +-------+------------+---------------+ | month | num_logins | num_downloads | +-------+------------+---------------+ | July | 20 | 11 | | June | 477 | 367 | | May | 71 | 15 | +-------+------------+---------------+ 3 rows in set (0.23 sec) I figure it's because month is related to logins, not downloads. However, it seems odd that it's the login numbers that are wonky, not downloads. If anyone can think of a way to do this in one query I'd really appreciate it. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---