4

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.

Giest
  • 485
  • 1
  • 9
  • 21
  • 4
    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

6 Answers6

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();
gvlasov
  • 16,604
  • 19
  • 65
  • 103
Insane Skull
  • 9,010
  • 9
  • 43
  • 61
3
->OrderBy("FIELD(id,3,4,2,1)");
AsgarAli
  • 2,165
  • 1
  • 19
  • 32
1

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);
Stas Panyukov
  • 328
  • 3
  • 7
0

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();
Zlocorp
  • 194
  • 2
  • 4
0
Product::find()->orderBy([new \yii\db\Expression('FIELD (id,3,1,2,4)'), 'id' => SORT_ASC])->all()
Janki Rathod
  • 107
  • 6
0

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();
Matteo Koi
  • 1