Yii2 Order By Relations

Question

I have the following database:

day: id
task: id
day_task: day_id, task_id, weight

Now I want to fetch all tasks from a specific day ordered by weight.

public function getTasks()
{
    return $this->hasMany(Task::className(), ['id' => 'task_id'])->viaTable('day_task', ['day_id' => 'id'], function ($query) {
        $query->orderBy(['weight' => SORT_ASC]);
    });
}

Resulting in:

SELECT * FROM `day_task` WHERE `day_id`=2 ORDER BY `weight`
SELECT * FROM `task` WHERE `id` IN ('2', '1', '3', '4')

The problem is that the DBMS returns the rows as they were stored in the table not considering the order of the IN and so I get '1', '2', '3', '4' instead of '2', '1', '3', '4'.

I could not find any solution apart from fetching it manually row by row.

please check this http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause — Anonymous Duck, May 08 '15 at 01:32
Yeah, I know about this but I don't know how to apply it to Yii — c1119, May 08 '15 at 01:39

score 1 · Answer 1 · edited Mar 21 '22 at 15:08

1

try add orderBy at the end query

public function getTasks()
{
   return $this->hasMany(Task::className(), ['id' => 'task_id'])
       ->viaTable('day_task', ['day_id' => 'id'])
       ->orderBy(['weight' => SORT_ASC]);
}

edited Mar 21 '22 at 15:08

robsch

9,358
9
63
104

answered Sep 04 '16 at 17:05

Đọc truyện hay

1,913
21
17

score 0 · Answer 2 · answered May 08 '15 at 06:47

I didn't tested this:

// in class Day:

public function getTasks() {
    return Task::find()
        ->leftJoin('day_task', 'task.id=day_task.task_id')
        ->where(['day_task.day_id' => $this->id])
        ->orderBy(['day_task.weight' => SORT_ASC]);
}

This should cause a query like:

SELECT task.* 
FROM   task LEFT JOIN day_task ON task.id = day_task.task_id 
WHERE  day_task.day_id = <id_of_day> 
ORDER  BY day_task.weight ASC

Is this true?

Yii2 Order By Relations

2 Answers2