Yii2 OrderBy specific field value first

Question

This is my table:

Product
id | name
1 | A
2 | B
3 | C
4 | D

And I want ID 3 in first position:

Product
id | name
3 | C
1 | A
2 | B
4 | D

I can only with the "OrderBy" assign ASC and DESC values. It gives error if you assign a numeric value.

I am not sure that I understand your question. You want to select the rows in this table and order them like in the second example? What would be the "ordering" criteria in this case? Why the row [3, C] should be the first one? — Marco Altieri, Jan 09 '16 at 02:10
http://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first but Yii2 — Giest, Jan 09 '16 at 02:12

score 14 · Accepted Answer · edited May 25 '21 at 10:24

14

Use yii\db\Expression :

$orderBy = (new \yii\db\Query())
         ->select('*')
         ->from('product')
         ->orderBy([new \yii\db\Expression('FIELD (id, 3,1,2,4)')])
         ->all();

edited May 25 '21 at 10:24

gvlasov

18,638
21
74
110

answered Jan 09 '16 at 04:30

Insane Skull

9,220
9
44
63

5

->orderBy([new \yii\db\Expression('FIELD (id, 3) DESC'), 'id' => SORT_ASC]) – Giest Jan 09 '16 at 15:22

score 3 · Answer 2 · answered Jan 09 '16 at 02:42

3

->OrderBy("FIELD(id,3,4,2,1)");

answered Jan 09 '16 at 02:42

AsgarAli

2,201
1
20
32

score 1 · Answer 3 · answered Mar 16 '17 at 08:15

You can push all order condtitions into array and then put this array into orderBy method like above. ta = enum type field.

$orderBy[] =  new \yii\db\Expression("ta = 'MALE' desc, ta = 'ALL' desc");
$orderBy[] =  new \yii\db\Expression("id asc, name desc");
$query->orderBy($orderBy);

score 1 · Answer 4 · edited Jan 18 '20 at 10:18

1

The sorting is possible also by the column's inner value, not only id. This can be helpful in case of an update. You update a value that must be at the bottom of your table, so:

->orderBy([new Expression('FIELD (column_name, value)ASC')])->all();

edited Jan 18 '20 at 10:18

answered Jan 18 '20 at 08:34

Matteo Koi

11
1

score 0 · Answer 5 · answered Jun 26 '18 at 20:56

If you looking how to sort with Fields with Yii2, try this

$array_ids = [5,7,3,9,2,8,1] //simple array

$modelsObj = Model::find()
        ->where(['id' => $array_ids]) // find only needed id's
        ->limit(5) //add limit if you need
        ->orderBy([new \yii\db\Expression('FIELD(id, '. implode(',', $array_ids) . ')')]) // sorting them as in array
        ->all();

Janki Rathod · Answer 6 · 2018-06-27T06:13:32.243

0

Product::find()->orderBy([new \yii\db\Expression('FIELD (id,3,1,2,4)'), 'id' => SORT_ASC])->all()

edited Jun 27 '18 at 06:13

answered Jun 27 '18 at 05:41

Janki Rathod

107
6

score 0 · Answer 7 · answered Feb 18 '23 at 16:00

This example code to sort country data ASC with the First column India

$data = Countries::find()
        ->orderBy([new \yii\db\Expression('FIELD (country_code, "IN") DESC, country_name ASC')])
        ->asArray()
        ->all();

output:

Array
(
    [0] => Array
        (
            [id] => 101
            [country_name] => India
            [country_code] => IN
        )

    [1] => Array
        (
            [id] => 18
            [country_name] => Bahrain
            [country_code] => BH
        )

    [2] => Array
        (
            [id] => 65
            [country_name] => Egypt
            [country_code] => EG
        )

    [3] => Array
        (
            [id] => 194
            [country_name] => Saudi Arabia
            [country_code] => SA
        )

)

Yii2 OrderBy specific field value first

7 Answers7