Selecting the newest record of a hasMany relationship

Question

I have 2 basic models in my CakePHP application: User and Login. A user has a hasMany relation with Logins (i.e., a new login record is created everytime the user logs in).

Now, I want to make 2 relations from the User model to the Login model: User hasMany Login and User hasOne lastLogin

This last relation should only include the last record of the Login model for the selected user.

I tried this as follows:

var $belongsTo = array
(
    'LastLogin' => array
    (
        'className' => 'Login',
        'order' => 'LastLogin.created DESC',
        'limit' =>  1

    )
);

However, this doesn't work. Any ideas on how to get this working?

And perhaps it should be 'order' => 'Login.created DESC' - as you are referring to the Login table? — Ross
I think you are over-doing model relationship. Why do you need a lastLogin when you can do a simple query on Login model to get one record? — Anh Pham
@Ross: Correct, I get one record per login, whereas I need one record per user. Also, I should refer to LastLogin instead of Login, asthis is the table alias. — Rick
@Anh Pham: I could do that, but I'd rather do a single query to get all the info I need from that person. When showing a list of users, I am only interested in the latest login of that user. — Rick

Mark Northrop Mark Northrop · Accepted Answer · 2011-10-02T11:03:17

UPDATED ANSWER IN RESPONSE TO COMMENTS

With a belongsTo relationship, the foreign key should be in the current model.

This means that if you want to have a relationship where User belongsTo LastLogin, the users table should have a last_login_id field.

In your case you probably want to use a hasOne relationship instead, and you're going to have to use the MAX() SQL function in the fields key. Note that getting the last_login works completely independently of your User hasMany Login relationship. So if all you want is the last login you can remove the hasMany relationship and just leave the hasOne.

With the example code below you'll get this:

Output of /users/index:

Array
(
    [User] => Array
        (
            [id] => 1
            [name] => user1
            [last_login] => 2011-05-01 14:00:00
        )
    [Login] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [user_id] => 1
                    [created] => 2011-05-01 12:00:00
                )
            [1] => Array
                (
                    [id] => 2
                    [user_id] => 1
                    [created] => 2011-05-01 13:00:00
                )
            [2] => Array
                (
                    [id] => 3
                    [user_id] => 1
                    [created] => 2011-05-01 14:00:00
                )
        )
)

If you don't use the Model::afterFind() callback your results will look more like this (Login array snipped to save space):

Array
(
    [User] => Array
        (
            [id] => 1
            [name] => user1
        )

    [0] => Array
        (
            [last_login] => 2011-05-01 14:00:00
        )
)

Example code:

users table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

logins table:

CREATE TABLE `logins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

User model:

class User extends AppModel {
    var $name = 'User';
    var $hasMany = array('Login');
    var $hasOne = array(
        'LastLogin' => array(
            'className' => 'Login',
            'fields' => array('MAX(LastLogin.created) as last_login')
        )
    );

    // This takes the last_login field from the [0] keyed array and puts it into 
    // [User]. You could also put this into your AppModel and it would work for 
    // all find operations where you use an SQL function in the 'fields' key.
    function afterFind($results, $primary=false) {
        if (!empty($results)) {
            foreach ($results as $i => $result) {
                if (!empty($result[0])) { // If the [0] key exists in a result...
                    foreach ($result[0] as $key => $value) { // ...cycle through all its fields...
                        $results[$i][$this->alias][$key] = $value; // ...move them to the main result...
                    }
                    unset($results[$i][0]); // ...and finally remove the [0] array
                }
            }
        }
        return parent::afterFind($results, $primary=false); // Don't forget to call the parent::afterFind()
    }
}

Users controller:

class UsersController extends AppController {
    var $name = 'Users';

    function index() {
        $this->autoRender = false;
        pr($this->User->find('all'));
    }
}

Selecting the newest record of a hasMany relationship

2 Answers