CODEWITHSUNDEEP
phpyii2
Vadim
Vadim

Reputation: 73

"Natural sorting" in Yii2

I have an Active Data Provider and GridView as a result where I need to sort teams according to their teamindex. I. e. A1 A2 A3..

Now I have 

<?php $dataProvider = new ActiveDataProvider([
    'query' => Team::find()->where(['tournament_id' => $model->id]),
    'pagination' => [
        'pageSize' => 30,
    ],
    'sort' => ['defaultOrder' => ['teamindex' => SORT_ASC, 'region_id' => SORT_ASC]],
]);

echo GridView::widget([
    'dataProvider' => $dataProvider,...

And the result is: A1 A10 A2 A3... I understand why strings are sorted this way but I can't find the solution to sort them in the way I need. Please give me some idea how to do it

Upvotes: 2

Views: 1621

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

you could try using a order by based on a calculated column eg:
coverting as integer the right part of you string (assuming that the column in named team_index)

CONVERT(substr(team_index,2), UNSIGNED INTEGER)

        CONVERT(substr(team_index,2), UNSIGNED INTEGER)

   'query' => Team::find()->where(['tournament_id' => $model->id])
    ->orderBy([ 'left(team_index,1)' => SORT_ASC,
               'CONVERT(substr(team_index,2), UNSIGNED INTEGER)'=>SORT_DESC]),

Upvotes: 1

eborrallo
eborrallo

Reputation: 750

Cast your column value to an integer explicitly with

order by cast(teamindex as unsigned) asc

In your code :

$dataProvider = new ActiveDataProvider([
            'query' => Team::find()
                ->where(['tournament_id' => $model->id])
                ->orderBy('cast(teamindex as unsigned) asc'),
            'pagination' => [
                'pageSize' => 30,
            ],

        ]);

Upvotes: 1

Related Questions