如何在 Laravel 中使用 Eloquent 最后对 NULL 值进行排序

How to sort NULL values last using Eloquent in Laravel(如何在 Laravel 中使用 Eloquent 最后对 NULL 值进行排序)

问题描述

我的员工和组表之间存在多对多关系.我已经创建了数据透视表，并且一切正常.但是，我的员工表上有一个 sortOrder 列，用于确定它们的显示顺序.sortOrder 列中值为 1 的员工应该排在第一位，值为 2 的员工排在第二位，依此类推.(如果按降序排序，则向后) sortOrder 列是一个允许空值的整数列.

I've got a many to many relationship between my employees and groups table. I've created the pivot table, and all is working correctly with that. However, I've got a sortOrder column on my employees table that I use to determine the order in which they display. Employee with a value of 1 in the sortOrder column should be first, value of 2 should be second, so on. (Or backwards if sorted descending) The sortOrder column is a integer column that allows null values.

我已经设置了我的组模型来按排序列对员工进行排序，但是我遇到了一个问题.始终首先显示空值.我已经尝试使用 ISNULL 和类似的 SQL 方法来代替使用的常规asc"或desc"，但我只得到一个错误.

I've set up my group model to sort the employees by the sort column, but I've run into a problem. The null values always are displayed first. I've tried using ISNULL and similar SQL methods in place of the regular "asc" or "desc" used, but I only get an error.

这是我的组模型中的代码:

Here's the code in my Group model:

class Group extends Eloquent {

public function employees()
    {
        return $this->belongsToMany("Employee")->orderBy('sortOrder', 'asc');
    }
}

这是我在控制器中用来访问我的模型的内容:

And here's what I use in the controller to access my model:

$board = Group::find(6)->employees;

Laravel 中最后对 NULL 值进行排序的技巧是什么?

What's the trick in Laravel to sorting NULL values last?

推荐答案

Laravel 没有考虑 ISNULL 方法，但是你可以将它作为原始查询传入并仍然使用它因为它比 IF 语句更有效，并且如果您的员工人数超过 1000000 人(已接受的答案)，结果将保持不变，如下所示:

Laravel does not take into consideration the ISNULL method however, you can pass it in as a raw query and still make use of it as it's more efficient than IF statements and the results will stay the same if you ever go beyond 1000000 employees (accepted answer), like so:

public function employees()
{
    return $this->hasMany('Employee')
                ->orderBy(DB::raw('ISNULL(sortOrder), sortOrder'), 'ASC');
}

更新:您还可以使用 orderByRaw() 方法:

Update: You can also use the orderByRaw() method:

public function employees()
{
    return $this->hasMany('Employee')
                ->orderByRaw('ISNULL(sortOrder), sortOrder ASC');
}

这篇关于如何在 Laravel 中使用 Eloquent 最后对 NULL 值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！