CODEWITHSUNDEEP
mysqlyii2
Liam
Liam

Reputation: 536

Yii2 Natural Sorting

I have been scratching my head and cannot for the life of me work out how to do the following

I need a VARCHAR filed to be able to be sorted in gridview using natural sorting i.e. number in order

SELECT * FROM test ORDER BY `number`;

Is how the system works at present. and will out put this

# id, number
1, 1
206, 10
97, 100
98, 102
99, 103
103, 104
104, 105
105, 106
106, 107
107, 108
108, 109
10, 11
109, 110
110, 111
111, 112

In MySQL if i want natural ordering then i can write this

SELECT * FROM test ORDER BY `number`+0;

I then get 

# id, number
1, 1
2, 2
3, 3
4, 4
5, 5
6, 6
7, 7
8, 8
9, 9
206, 10
10, 11
11, 12
12, 13

So the number is in order.

How can i achieve this in Yii2?

Regards

Liam

From the answer below i was able to use the following in the search model

    $dataProvider->sort->attributes['number'] = 
    [
        'asc' => ['LENGTH(number)' => SORT_ASC, 'number'=>SORT_ASC],
        'desc' => ['LENGTH(number)' => SORT_DESC, 'number'=>SORT_DESC],
    ];

Upvotes: 1

Views: 993

Answers (1)

Bizley
Bizley

Reputation: 18021

Try this in DataProvider configuration (I'm not sure if you can pass MySQL function instead of simple column name).

// ...
'sort' => [
    'defaultOrder' => [
        'LENGTH(number)' => SORT_ASC,
        'number' => SORT_ASC,
    ],
],

In general checking length first and value second should give you natsort effect.

Upvotes: 5

Related Questions