我有一个warehouse表。它有一个名为level的列。我想按升序排序，如果level为空，那么它应该根据id降序排序。
例如，我的表有以下记录。
+----+-----------+-------+
| id | name | level |
+----+-----------+-------+
| 1 | osaka | 3 |
+----+-----------+-------+
| 2 | tokyo | null |
+----+-----------+-------+
| 3 | sapporo | null |
+----+-----------+-------+
| 4 | nagoya | 4 |
+----+-----------+-------+
| 5 | hiroshima | 1 |
+----+-----------+-------+
首先，根据级别列，它应该对广岛->大阪->名古屋进行排序。其余为空。因此，它们将根据 id 列降序排序。所以，它将是札幌>东京。 因此，最终排序结果将是广岛->大阪->名古屋->札幌->东京。
到目前为止，我已经尝试过，
$warehouses = Warehouse::orderby('level','asc)
->pluck('name');
dd($warehouses);
这显然是行不通的。但我不确定如何前进。我正在使用PGSQL。
我在SO中发现了类似的问题。我已经基于此尝试了一些原始查询。还是不能解决问题。
像这样的事情呢：
- 从表中获取所有内容。
- 使用收集的结果获得所有没有空级别的结果，然后对它们进行排序。
- 使用收集的结果获取所有空级别，然后对其进行排序。
- 合并 2 个排序的集合结果并提取名称。
// 1. Get all from the table.
$warehouses = Wharehouse::all();
// 2. Using the collected results get all without null levels then sort them.
$warehousesWithLevels = $warehouses->where('level', '!=', null)
->sortBy('level');
// 3. Using the collected results get all with null levels then sort them.
$warehousesWithoutLevels = $warehouses->where('level', null)
->sortByDesc('id');
// 4. Merge the 2 sorted collection results and pluck the name.
$warehousesSorted = $warehousesWithLevels->merge($warehousesWithoutLevels)->pluck('name');
dd($warehousesSorted);
或者使用我在模型中创建的范围，您可以使用：
Wharehouse::allSortedMyWay();
上面有一个数据库查询，然后处理收集的结果。
您可以修改最适合您需求的任何键的排序。
经测试，结果如下：
Collection {#268 ▼
#items: array:5 [▼
0 => "hiroshima"
1 => "osaka"
2 => "nagoya"
3 => "sapporo"
4 => "tokyo"
]
}
我使用的模型：
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Wharehouse extends Model
{
/**
* The database table used by the model.
*
* @var string
*/
protected $table = 'warehouses';
/**
* Indicates if the model should be timestamped.
*
* @var bool
*/
public $timestamps = false;
/**
* The attributes that are not mass assignable.
*
* @var array
*/
protected $guarded = [
'id',
];
/**
* Fillable fields for a Profile.
*
* @var array
*/
protected $fillable = [
'name',
'level',
];
}
/**
* Return all warehousts sorted my way - Quick but not a true query scope.
*
* @return collection
*/
public function scopeAllSortedMyWay()
{
$warehouses = Wharehouse::all();
$warehousesWithLevels = $warehouses->where('level', '!=', null)
->sortBy('level');
$warehousesWithoutLevels = $warehouses->where('level', null)
->sortByDesc('id');
return $warehousesWithLevels->merge($warehousesWithoutLevels)->pluck('name');
}
我使用的播种机：
<?php
use AppWharehouse;
use IlluminateDatabaseSeeder;
class WharehouseTableSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$items = [
[
'name' => 'osaka',
'level' => 3,
],
[
'name' => 'tokyo',
'level' => null,
],
[
'name' => 'sapporo',
'level' => null,
],
[
'name' => 'nagoya',
'level' => 4,
],
[
'name' => 'hiroshima',
'level' => 1,
],
];
foreach ($items as $item) {
$newItem = Wharehouse::where('name', '=', $item['name'])->first();
if ($newItem === null) {
$newItem = Wharehouse::create([
'name' => $item['name'],
'level' => $item['level'],
]);
}
}
}
}
我使用的迁移：
<?php
use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class CreateWarehouseTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('warehouses', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name')->unique();
$table->integer('level')->nullable();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('warehouses');
}
}
使用以下，所有空值项都将是最后一个
SELECT * FROM warehouse ORDER BY ISNULL(level), level ASC;
这是 SQLFiddle
您可以使用：
SELECT
*
FROM
warehouse
ORDER BY
CASE WHEN level IS null THEN 1 ELSE 0 END, level, id;
你可以试试 这里