CODEWITHSUNDEEP
databaseactiverecordyii2
JoseCarlosPB
JoseCarlosPB

Reputation: 875

Sorty by calculated field with active record yii2

I have threads and messages on thread

I want to return all threads with the last message time, so I added a new field like this on thread model

  public function fields()
  { 
    $fields= ['idThread', 'idUser', 'title', 'unread', 'username','lastMesageTime'];
    return $fields;
   }

now with this method I get the calculated value lastMessageTime

  public function getLastMessageTime()
   {
       return $this->hasMany(Messages::className(), ['idThread' => 'idThread'])
      ->select('time')->orderBy('time DESC')->limit(1)->scalar();
   }

on my index method using active record like this

return Thread::find()->select('idThread, title, idUser')->all();

this works and I get lastMessageTime with the right value, but I want to order by so I can get the thread with the most recent lastMessageTime the first one, I tried with the following code

  public function scopes() {
    return array(
        'byOrden' => array('order' => 'lastTimeMessage DESC'),
    );
}

any idea?

Edit: this workaround works, but I think this is not a good way because I'm not using active record so fields like username that I had defined on Thread model I had to fetch it again

 $query = (new \yii\db\Query());

    $query->select('*,  (SELECT max(time) as lastMessageTime  from messages where messages.idThread = thread.idThread ) lastMessageTime,
    (SELECT name from users where users.idUser = thread.idUser) as name ')
        ->from('threads')
        ->where(['idUser'=>$idUser])
        ->orderBy('lastMessageTime DESC');

    $rows = $query->all();
    return $rows;

Upvotes: 0

Views: 64

Answers (1)

Michal Hynčica
Michal Hynčica

Reputation: 6144

You can define extra fields as model properties, then override find method to load data for them.

class Thread extends \yii\db\ActiveRecord
{
    public $lastMessageTime;

    public static function find()
    {
        $q = parent::find()
            ->select('*')
            ->addSelect(
                 new \yii\db\Expression(
                     '(SELECT max(time) FROM messages WHERE messages.idThread = thread.idThread) AS lastMessageTime'
                 );
        return $q;
    }
}

Then you can load and order models like this:

$rows = Thread::find()->orderBy(['lastMessageTime' => SORT_DESC])->all();

Upvotes: 1

Related Questions