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