Yii2: how to order activeRecord relation query by related field?

Question

I want to execute this query:

$topics = ForumTopic::find()
            ->with([                
                'lastPost' => function($query) {
                    $query->select(['id', 'ctime']);
                },
            ])
            ->orderBy('lastPost.ctime DESC')
            ->all();

Relation declared in ForumTopic like this:

public function getLastPost()
{
    return $this->hasOne(ForumPost::className(), ['id' => 'lastPostId']);
}

But my query fails (because yii2 make all queries separate and don't join tables). Is any way to achive my purpose by yii2 activeRecord?

Bizley · Answer 1 · 2017-08-11T11:58:10.217

0

Try this:

$topics = ForumTopic::find()
        ->with([                
            'lastPost' => function($query) {
                $query->select(['id', 'ctime']);
            },
        ])
        ->orderBy([ForumPost::tableName() . '.ctime' => SORT_DESC])
        ->all();

edited Aug 11 '17 at 11:58

answered Aug 11 '17 at 10:41

Bizley

17,392
5
49
59

`lastPostId` is the field of **ForumTopic** model (not ForumPost). This is actually mentioned in relation declaration (this is described in the Guide: `An easy rule to remember this is, as you see in the example above, you write the column that belongs to the related Active Record directly next to it.`). – Degibons Aug 11 '17 at 11:42
So how it helps to retrieve `lastPostId` in lastPost relation select? – Degibons Aug 11 '17 at 11:49
Sorry, my answer was too hasty, I've updated it, try now. – Bizley Aug 11 '17 at 11:58
This is also didn't worked out. Exception tells `Column not found: 1054 Unknown column 'forum_post.ctime' in 'order clause' The SQL being executed was: SELECT * FROM forum_topic ORDER BY forum_post.ctime` As I mentioned in my question the main problem is that yii2 activeRecord don't join tables in eager loading (it makes separate queries). (In yii1 it did it.) – Degibons Aug 11 '17 at 12:08
Try `joinWith` instead `with`. – Bizley Aug 11 '17 at 12:14

score 0 · Accepted Answer · answered Aug 12 '17 at 13:35

So, just for clarification, the right answer is:

$topics = ForumTopic::find()
            ->alias('t')
            ->select(['t.*','l.ctime lastPostCtime'])
            ->joinWith([
                'lastPost l' => function($query) {
                    $query->select(['l.id', 'l.ctime', 'l.userId', 'l.guestName']);
                },
            ])
            ->orderBy('lastPostCtime DESC')
            ->all();

Yii2: how to order activeRecord relation query by related field?

2 Answers2