On 07/11/2009 02:06 PM, brian wrote:
> 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.
>
> >
>
Try this and let me know if it works:
SELECT MONTHNAME(l.created) AS month, COUNT(l.id) AS
num_logins, COUNT(d.id) AS num_downloads FROM logins AS l INNER JOIN
downloads AS d ON d.login_id = l.id GROUP BY month;
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
