Alternatively, you could do this (uses a subquery)
$this->User->bindModel(array('hasOne' => array(
'FirstLogin' => array('className' => 'LoginLog', 'conditions' =>
'(`FirstLogin`.`created`=(SELECT MIN(created) FROM login_logs WHERE
user_id=`User`.`id`))'),
'LastLogin' => array('className' => 'LoginLog', 'conditions' =>
'(`LastLogin`.`created`=(SELECT MAX(created) FROM login_logs WHERE
user_id=`User`.`id`))')
)));
$users = $this->User->find('all');
This should be relatively fast if your indexes are set up correctly.
If you only need the modified field, then Zoe / MonkeyGirl's solution
would probably be better, although you won't get automagic recursive
stuff with a query() call. If you want both, you could do it this way:
$this->User->bindModel(array('hasOne' => array('FirstAndLastLogin' =>
array('className' => 'LoginLog')));
$users = $this->User->find(
'all', array(
'fields' => array('User.id', 'User.name',
'MAX(FirstAndLastLogin.created) AS last_login',
'MIN(FirstAndLastLogin.created) AS min_first_login'),
'conditions' => '1=1 GROUP BY User.id'
)
);
Which will give you a result like this:
$users = array(
0 => array(
'User' => array(
'id' => 1234,
'name' => 'Testy McTest'
),
'FirstAndLastLogin' => array(
'last_login' => '...',
'first_login' => '...',
)
),
1 => ...
);
But will also generate hasMany / hasAndBelongsToMany queries if you
set the recursive var high enough.
Hope this helps
grigri
Gotta ask by the way - why the 'modified' field in a login table? Are
you planning to update it at all? Seems to me like a login row should
be unmodifiable once it's been created.
On Jan 28, 12:47 pm, MonkeyGirl <[EMAIL PROTECTED]> wrote:
> > In my admin area I want to show the following data:
>
> > name, created, 1st Login, Last Login
>
> Hiya!
>
> This is probably one of those rare instances where a big ol' SQL query
> would probably be easier than doing it at a higher level with CakePHP.
> Something like this in the users' model might help:
>
> function findUsersWithLoginInfo() {
> $this->query("SELECT User.name, User.created, MIN(LoginLog.created),
> MAX(LoginLog.created)
> FROM users AS User
> LEFT JOIN login_logs AS LoginLog ON User.id = LoginLog.user_id
> GROUP BY User.id");
>
> }
>
> Then in the users' controller, you can simply assign the output of
> $this->User->findUsersWithLoginInfo() to a variable.
>
> Hope that helps,
> Zoe.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" 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
-~----------~----~----~----~------~----~------~--~---
