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 [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
-~----------~----~----~----~------~----~------~--~---
