Yii model relation one-to-newest

Question

Assume we have 3 tables like this:

---------    ---------------    -------------------
| USER  |    | USER_STATUS |    | STATUS_DICT     |
|-------|    |--------------    |------------------
| id    |    | id          |    | id (int)        |
| name  |    | user_id     |    | status (string) |
| email |    | status_id   |    -------------------
---------    | timestamp   |
             ---------------

Each user has many statuses (user_status), related with user.id <-> user_status.user_id. Each user_status has one related record in status_dict which is string label of int status (for example 0 = active).

What I want is make User model to retrieve last user's status using relation. Set relations:

/* User model */
public function relations() {
    return array(
        'status' => array(self::HAS_ONE, 'UserStatus', 'user_id', 'order'=>'status.id DESC'),
    );
}

and

/* UserStatus model */
public function relations() {
    return array(
        'status' => array(self::BELONGS_TO, 'StatusDictionary', 'status_id'),
        'user' => array(self::BELONGS_TO, 'User', 'user_id'),
    );
}

Then when I call User::model()->findByPk(1)->status I get latest status for user #1.

BUT.

When I want to find all users with specified status it does not work as I want. Instead of getting only users with specified status (latest record in user_status) I get all users who at least once had this status.

What I have to do to make relation one-to-newest (I call it like this for this case's purpose)? I saw this article and it does the job, but I'm wondering if there is way to achieve it with Yii's conventions.

I wanted to declare named scope like this:

/* in User model */
public function withStatus($status) {
    if(!is_numeric($status) && !is_null($status)) {
        $status = StatusDictionary::model()->find('lower(name)=lower(:name)', array(':name' => $status))->getAttribute('id');
    }

    $this->getDbCriteria()->mergeWith(array(
        'condition' => (is_null($status) ? 'status.status_id IS NULL' : 'status.status_id=:statusId'),
        'params' => array(':statusId' => $status),
        'with' => array('status')
    ));

    return $this;
}

But it doesn't work either, User::model()->withStatus(0)->findAll() returns all users with related record in user_status with user_status.status_id = 0 even if some of users have newer, different status.

Thanks in advance for any help.

Answer 1

Ok, I found solution with TheRifler's help from his post. It turned out that I had a solution right under my nose (article linked in a question).

Relation should be defined as below:

/* User model */
public function relations() {
    return array(
        'status' => array(self::HAS_ONE, 'UserStatus', 'user_id',
            'order' => 's.id DESC',
            'alias' => 's',
            // http://murrayhopkins.wordpress.com/2008/10/28/mysql-left-join-on-last-or-first-record-in-the-right-table/
            'join' => 'LEFT JOIN ( SELECT s1.*
                        FROM `user_status` as s1
                        LEFT JOIN `user_status` AS s2
                        ON s1.user_id = s2.user_id AND s1.id < s2.id
                        WHERE s2.user_id IS NULL ) as `status`
                        ON (s.user_id = `status`.user_id)'
            ),
    );
}

and the named scope:

public function withStatus($status) {
    if(!is_numeric($status) && !is_null($status)) {
        $status = StatusDictionary::model()->find('lower(name)=lower(:name)', array(':name' => $status))->getAttribute('id');
    }

    $this->with('status');
    if(!is_null($status)) {
        $this->getDbCriteria()->compare('status.status_id', $status);
    }
    else {
        $this->getDbCriteria()->addCondition('s.status_id IS NULL');
    }

    return $this;
}

It contains some dirty work, because withStatus() uses status join subquery's data if $status is specified or base s alias if $status is null (it's because we're looking for user without record in status table). Maybe this is not most elegant solution, but works as it should.

Now I can use:

• User::model()->findByPk(1)->status to access specified user's status
• User::model()->withStatus('active')->findAll() list users with specified status (status may be passed as integer [user_status.id] or string [status_dict.name])
• User::model()->withStatus(null)->count() get number of users without any status

Answer 2

I found somewhere else similar solution:

Scope function:

public function withStatus($status) {
    if(!is_numeric($status) && !is_null($status)) {
        $status = StatusDictionary::model()->find('lower(name)=lower(:name)', array(':name' => $status))->getAttribute('id');
    }

    $this->with('status');
    $this->getDbCriteria()->compare('i.statusId', $status);

    return $this;
}

Relations record (this join is protecting relation from filter criteria):

'status' => array(self::HAS_ONE, 'Status', 'clientId',
                'join' => 'INNER JOIN (select max(id) id from status group by clientId) j ON i.id=j.id',
                'alias' => 'i'
            ),

It works for me.