如何在 Laravel 中使用 Eloquent 最后对 NULL 值进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17644072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort NULL values last using Eloquent in Laravel
提问by eagle0042
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.
我的员工和组表之间存在多对多关系。我已经创建了数据透视表，并且一切正常。但是，我的员工表上有一个 sortOrder 列，用于确定它们的显示顺序。sortOrder 列中值为 1 的员工应该排在第一位，值为 2 的员工排在第二位，依此类推。（如果按降序排序，则向后） sortOrder 列是一个允许空值的整数列。
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.
我已经设置了我的组模型来按排序列对员工进行排序，但是我遇到了一个问题。始终首先显示空值。我已经尝试使用 ISNULL 和类似的 SQL 方法来代替使用的常规“asc”或“desc”，但我只得到一个错误。
Here's the code in my Group model:
这是我的 Group 模型中的代码：
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;
What's the trick in Laravel to sorting NULL values last?
Laravel 最后对 NULL 值进行排序的技巧是什么？
采纳答案by Andreyco
public function employees()
{
return $this
->hasMany('Employee')
->select(['*', DB::raw('IF(`sortOrder` IS NOT NULL, `sortOrder`, 1000000) `sortOrder`')])
->orderBy('sortOrder', 'asc');
}
Explanation:
The IF statement solves the issue here. If NULL value is found, some big number is assigned to sortOrder instead. If found not NULL value, real value is used.
说明：
IF 语句解决了此处的问题。如果发现 NULL 值，则会将一些大数字分配给 sortOrder。如果发现不是 NULL 值，则使用实际值。
回答by junkystu
Laravel does not take into consideration the ISNULLmethod however, you can pass it in as a raw query and still make use of it as it's more efficient than IFstatements and the results will stay the same if you ever go beyond 1000000 employees (accepted answer), like so:
Laravel 没有考虑该ISNULL方法，但是，您可以将其作为原始查询传入并仍然使用它，因为它比IF语句更有效，并且如果您超过 1000000 名员工（已接受的答案），结果将保持不变，像这样：
public function employees()
{
return $this->hasMany('Employee')
->orderBy(DB::raw('ISNULL(sortOrder), sortOrder'), 'ASC');
}
Update:You can also use the orderByRaw()method:
更新：您还可以使用orderByRaw()方法：
public function employees()
{
return $this->hasMany('Employee')
->orderByRaw('ISNULL(sortOrder), sortOrder ASC');
}
回答by miller
Just add a minus sign to field and change order to DESC.
只需在字段中添加一个减号并将顺序更改为 DESC。
$q->orderBy(\DB::raw('-`sortOrder`'), 'desc');
回答by shukshin.ivan
In Laravel 5.2 or higher just call orderByRaw. You even able to sort through aggregated value rather than a column. In the following example max_stcan be nullif there is no submodels.
在 Laravel 5.2 或更高版本中，只需调用orderByRaw. 您甚至可以对聚合值而不是列进行排序。在下面的例子中max_st可以是null如果没有子模型。
Model::where('act', '2')
->leftJoin('submodels', 'model.id', '=', 'submodels.model_id')
->select('models.*', DB::raw('MAX(submodels.st) as max_st')),
->orderByRaw('max_st DESC NULLS LAST');
回答by Juliusz Gonera
Instead of relying on an arbitrary large number you can also do:
除了依赖任意大数，您还可以执行以下操作：
public function employees()
{
return $this
->hasMany('Employee')
->select(['*', DB::raw('sortOrder IS NULL AS sortOrderNull')])
->orderBy('sortOrderNull')
->orderBy('sortOrder');
}
It has an added benefit of being supported by SQLite.
SQLite 支持它还有一个额外的好处。
回答by Fillie
I ran into this problem recently using Laravel 5.6, where junkystu answer was perfect for me. However our testing framework uses sqlite, so tests were constantly returning a 500 error.
我最近使用 Laravel 5.6 遇到了这个问题，junkystu 的回答对我来说是完美的。但是我们的测试框架使用 sqlite，因此测试不断返回 500 错误。
This is what we came up with, which should be slightly more agnostic of a DB driver.
这就是我们想出的，它应该与数据库驱动程序稍微不可知。
Ascending
上升
$query->orderBy(DB::raw('column_to_sort IS NULL, column_to_sort'), 'asc');
Descending
降序
$query->orderBy(DB::raw('column_to_sort IS NOT NULL, column_to_sort'), 'desc');
回答by AlexM
A workaround for PostgreSQL
PostgreSQL 的解决方法
For numeric types:
对于数字类型：
DB::table('t')
->select(['id', 'val'])
->orderBy(DB::raw("coalesce(val, 0)"), 'desc')
For text types:
对于文本类型：
orderBy(DB::raw("coalesce(val, '')"), 'desc')
The trick is to replace NULLvalues in the sorting column to zero (or empty string) so that it could be sorted as an ordinary integer (or text) value.
诀窍是NULL将排序列中的值替换为零（或空字符串），以便将其排序为普通整数（或文本）值。
回答by Bruce van der Kooij
->orderBy('sortOrder', 'is', 'null')->orderBy('sortOrder', 'asc')
Seems to work.
似乎工作。
