"Natural sorting" in Yii2

Question

我有一个活动数据提供程序和 GridView，因此我需要根据团队索引对团队进行排序。答1答2回答 3..

现在我有

<?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,...

其结果是 ：A1 A10 A2 A3...我理解为什么字符串以这种方式排序，但我找不到以我需要的方式对它们进行排序的解决方案。请给我一些想法怎么做

Answer 1

您可以尝试使用基于计算列的顺序，例如：
将字符串的右侧部分覆盖为整数(假设 中的列名为 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]),

Answer 2

将列值显式转换为整数

order by cast(teamindex as unsigned) asc

在您的代码中：

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