问题:如何根据空值对表进行排序?

我有一张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 中发现了类似的问题。我已经尝试了一些基于此的原始查询。仍然无法解决问题。

解答

像这样的东西怎么样:

1. 从桌子上拿走所有东西。

2. 使用收集的结果得到所有没有空值的级别,然后对它们进行排序。

3. 使用收集的结果获取所有空级别,然后对它们进行排序。

4. 将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 Illuminate\Database\Eloquent\Model;

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 App\Wharehouse;
use Illuminate\Database\Seeder;

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 Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

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');
    }
}