CASE WHEN in ActiveQuery

Yii 2.0
1

Hello everybody.
I’m creating a chat system. The db table is quite simple:

# Nome Tipo Codifica caratteri Attributi Null Predefinito Commenti Extra
1 idPrimaria int(11) No Nessuno AUTO_INCREMENT
2 recipient_id smallint(6) No Nessuno
3 sender_id smallint(6) No Nessuno
4 seen tinyint(1) No 0
5 sent_on datetime No CURRENT_TIMESTAMP
6 text text latin1_swedish_ci No Nessuno

Now I’d like to get just the users the active user has chatted with. I’ve created this query, but not having a “other_id” in the model, it returns me an error.

public static function listAllChats()
{
    return $this->select(['CASE WHEN `recipient_id`='.Yii::$app->user->id.' THEN `sender_id` ELSE `recipient_id` END AS `other_id`','MAX(sent_on) AS sent_on'])
            ->from('message')
            ->where(['recipient_id' => Yii::$app->user->id])
            ->orWhere(['or', 'sender_id' => Yii::$app->user->id])
            ->groupBy('other_id')->orderBy(['sent_on' => SORT_DESC])->all();        
}
/* In the view */
    $chats = Message::listAllChats();
    foreach ($chats as $chat)
    {
    echo $chat->other_id.'<br>;
    }

How can I extract the “other_id” field from this query? :thinking:

2

Once again I reply to myself :rofl:
In the view I replaced

$chats = Message::listAllChats();

with

$command = (new \yii\db\Query())
        ->select(['CASE WHEN `recipient_id`='.Yii::$app->user->id.' THEN `sender_id` ELSE `recipient_id` END AS `other_id`','MAX(sent_on) AS sent_on'])
        ->from('message')
        ->where(['recipient_id' => Yii::$app->user->id])
        ->orWhere(['sender_id' => Yii::$app->user->id])
        ->groupBy('other_id')->orderBy(['sent_on' => SORT_DESC])
        ->createCommand();
$chats = $command->queryAll();
foreach ($chats as $chat)
{
echo $chat['other_id'].'<br>';
}