On 07/12/2009 07:16 PM, Martin Radosta wrote:
> 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;
>
>
Sorry, did'nt read your problem in detail, try this query:
|SELECT MONTH , min( num_downloads ) AS num_downloads, min( num_logins )
AS num_logins
FROM (
SELECT MONTHNAME( created ) AS
MONTH , COUNT( 1 ) AS num_downloads, NULL AS num_logins
FROM downloads
GROUP BY MONTH
UNION
SELECT MONTHNAME( created ) AS
MONTH , NULL AS num_downloads, COUNT( 1 ) AS num_logins
FROM logins
GROUP BY MONTH
) AS q
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
