If you really want the fastest way to retrieve the data (but slower
updates) then you should cache the fields in the `users` table, and
update them in LoginLog::afterSave(), something like this:
class LoginLog extends AppModel {
// ...
function afterSave($created) {
if (!empty($this->data[$this->alias]['user_id'])) {
// We'll always update the last_login
$fields = array('last_login' => $this->data[$this->alias]
['created']);
$conditions = array('id' => $this->data[$this->alias]
['user_id']);
// If this is the only login for this user then it's his first
if ($this->findCount(array($this->alias . '.user_id' => $this-
>data[$this->alias]['user_id']), -1) == 1) {
$fields['first_login'] = $this->data[$this->alias]['created'];
}
$this->User->updateAll($fields, $conditions);
}
}
}
On Jan 28, 1:16 pm, Mike Digital Egg <[EMAIL PROTECTED]> wrote:
> Hi Grigri,
>
> Thanks for your reply, I will give yours a go as well and see which is
> faster.
>
> The modified field is indeed unneccessary, I just add those fields as
> a habit, but you are right that the login log should never be
> modified :)
>
> Cheers
>
> Mike
>
> On Jan 28, 2:10 pm, grigri <[EMAIL PROTECTED]> wrote:
>
> > 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
-~----------~----~----~----~------~----~------~--~---
