blocks|key|2036641|text|public+function+employees()
{
++++return+$this
++++++++->hasMany('Employee')
++++++++->select(['*',+DB::raw('IF(`sortOrder`+IS+NOT+NULL,+`sortOrder`,+1000000)+`sortOrder`')])
++++++++->orderBy('sortOrder',+'asc');
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2036642|说明：|unstyled|offset|length|style|BOLD|2036643|IF语句在这里解决了这个问题。如果找到空值，则将某个较大的数字分配给sortOrder。如果发现非空值，则使用实值。|2036644|entityMap^0|0|0|3|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>public function employees()
{
 return $this
 -&gt;hasMany('Employee')
 -&gt;select(['*', DB::raw('IF(`sortOrder` IS NOT NULL, `sortOrder`, 1000000) `sortOrder`')])
 -&gt;orderBy('sortOrder', 'asc');
}
</code></pre>

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.

blocks|key|2033252|text|你也可以这样做，而不是依赖于一个任意的大数字：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033253|public+function+employees()
{
++++return+$this
++++++++->hasMany('Employee')
++++++++->select(['*',+DB::raw('sortOrder+IS+NULL+AS+sortOrderNull')])
++++++++->orderBy('sortOrderNull')
++++++++->orderBy('sortOrder');
}|code-block|syntax|javascript|2033254|它还有一个额外的好处，那就是得到了SQLite的支持。|2033255|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Instead of relying on an arbitrary large number you can also do:

<pre><code>public function employees()
{
 return $this
 -&gt;hasMany('Employee')
 -&gt;select(['*', DB::raw('sortOrder IS NULL AS sortOrderNull')])
 -&gt;orderBy('sortOrderNull')
 -&gt;orderBy('sortOrder');
}
</code></pre>

It has an added benefit of being supported by SQLite.

blocks|key|896018|text|->orderBy('sortOrder',+'is',+'null')->orderBy('sortOrder',+'asc')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|896019|看起来很管用。|unstyled|896020|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>-&gt;orderBy('sortOrder', 'is', 'null')-&gt;orderBy('sortOrder', 'asc')
</code></pre>

Seems to work.

blocks|key|2422610|text|Laravel没有考虑ISNULL方法，但是，您可以将其作为原始查询传递，并仍然使用它，因为它比IF语句更有效，并且如果超过1000000个员工(可接受的答案)，结果将保持不变，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2422611|public+function+employees()
{
++++return+$this->hasMany('Employee')
++++++++++++++++->orderBy(DB::raw('ISNULL(sortOrder),+sortOrder'),+'ASC');
}|code-block|syntax|javascript|2422612|更新：您还可以使用orderByRaw()方法：|BOLD|2422613|public+function+employees()
{
++++return+$this->hasMany('Employee')
++++++++++++++++->orderByRaw('ISNULL(sortOrder),+sortOrder+ASC');
}|2422614|entityMap|0|LINK|mutability|MUTABLE|url|https://laravel.com/docs/queries#raw-methods^0|B|6|1C|2|0|0|0|3|9|C|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@$9|14|A|15|B|M]]|D|@$9|16|A|17|1|18]]|E|$]]|$1|N|3|O|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|P|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|Q|$R|$5|S|T|U|E|$V|W]]]]

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

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

Update:
You can also use the <a href="https://laravel.com/docs/queries#raw-methods" rel="noreferrer">orderByRaw()</a> method:

<pre><code>public function employees()
{
 return $this-&gt;hasMany('Employee')
 -&gt;orderByRaw('ISNULL(sortOrder), sortOrder ASC');
}
</code></pre>

blocks|key|2033297|text|只需在字段中添加减号并将订单更改为DESC即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2033298|$q->orderBy(\DB::raw('-`sortOrder`'),+'desc');|code-block|syntax|javascript|2033299|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Just add a minus sign to field and change order to DESC.

<pre><code>$q-&gt;orderBy(\DB::raw('-`sortOrder`'), 'desc');
</code></pre>

blocks|key|2036734|text|PostgreSQL的解决方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2036735|对于数值类型：|2036736|DB::table('t')
++++->select(['id',+'val'])
++++->orderBy(DB::raw("coalesce(val,+0)"),+'desc')|code-block|syntax|javascript|2036737|对于文本类型：|2036738|orderBy(DB::raw("coalesce(val,+'')"),+'desc')|2036739|诀窍是将排序列中的NULL值替换为零(或空字符串)，以便可以将其作为普通整数(或文本)值进行排序。|offset|length|style|CODE|2036740|entityMap^0|0|0|0|0|0|9|4|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@$O|10|P|11|Q|R]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

A workaround for PostgreSQL 

For numeric types:

<pre><code>DB::table('t')
 -&gt;select(['id', 'val'])
 -&gt;orderBy(DB::raw("coalesce(val, 0)"), 'desc')
</code></pre>

For text types:

<pre><code>orderBy(DB::raw("coalesce(val, '')"), 'desc')
</code></pre>

The trick is to replace <code>NULL</code> values in the sorting column to zero (or empty string) so that it could be sorted as an ordinary integer (or text) value.

blocks|key|2036746|text|在Laravel+5.2或更高版本中，只需调用orderByRaw即可。您甚至可以按聚合值排序，而不是按列排序。在下面的示例中，如果没有子模型，则max_st可以为null。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2036747|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');|code-block|syntax|javascript|2036748|entityMap^0|N|A|21|6|2A|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

In Laravel 5.2 or higher just call <code>orderByRaw</code>. You even able to sort through aggregated value rather than a column. In the following example <code>max_st</code> can be <code>null</code> if there is no submodels.

<pre><code>Model::where('act', '2')
 -&gt;leftJoin('submodels', 'model.id', '=', 'submodels.model_id')
 -&gt;select('models.*', DB::raw('MAX(submodels.st) as max_st')),
 -&gt;orderByRaw('max_st DESC NULLS LAST');
</code></pre>

blocks|key|892227|text|我最近在使用Laravel+5.6时遇到了这个问题，在那里junkystu的答案对我来说是完美的。然而，我们的测试框架使用的是sqlite，所以测试总是返回500错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|892228|这就是我们想出来的，这应该是一个稍微不可知的DB驱动程序。|892229|递增的|offset|length|style|BOLD|892230|$query->orderBy(DB::raw('column_to_sort+IS+NULL,+column_to_sort'),+'asc');|code-block|syntax|javascript|892231|降序|892232|$query->orderBy(DB::raw('column_to_sort+IS+NOT+NULL,+column_to_sort'),+'desc');|892233|entityMap^0|0|0|0|3|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@$F|X|G|Y|H|I]]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|10|8|@$F|11|G|12|H|I]]|9|@]|A|$]]|$1|Q|3|R|5|L|7|13|8|@]|9|@]|A|$M|N]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

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.

This is what we came up with, which should be slightly more agnostic of a DB driver.

Ascending

<pre><code>$query-&gt;orderBy(DB::raw('column_to_sort IS NULL, column_to_sort'), 'asc');
</code></pre>

Descending

<pre><code>$query-&gt;orderBy(DB::raw('column_to_sort IS NOT NULL, column_to_sort'), 'desc');
</code></pre>

blocks|key|2422847|text|为了获得更好的效果，你可以像下面这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2422848|2422849|这将从最新到最旧排序，从空到最后排序。|ordered-list-item|2422850|2422851|->orderByRaw("CASE+WHEN+column_to_order+IS+NULL+THEN+0+ELSE+1+END+DESC")+->orderBy('column_to_order'，'DESC')|2422852|2422853|这将首先排列空记录，然后排列最旧记录到最新记录。|2422854|2422855|+++->orderByRaw("CASE+WHEN+column_to_order+IS+NULL+THEN+0+ELSE+1+END+ASC")
+++->orderBy('column_to_order',+'ASC')|code-block|syntax|javascript|2422856|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|V|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|X|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|Z|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|11|8|@]|9|@]|A|$P|Q]]|$1|R|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|S|$]]

More elegantly you can do as below for better results
<ol>
<li>This will order latest to oldest and null to the last.
<pre><code> -&gt;orderByRaw(&quot;CASE WHEN column_to_order IS NULL THEN 0 ELSE 1 END DESC&quot;)
 -&gt;orderBy('column_to_order', 'DESC')

</code></pre>
</li>
<li>This will arrange null records first and then oldest to latest.
</li>
</ol>
<pre><code> -&gt;orderByRaw(&quot;CASE WHEN column_to_order IS NULL THEN 0 ELSE 1 END ASC&quot;)
 -&gt;orderBy('column_to_order', 'ASC')
</code></pre>

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.

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:

<pre><code>class Group extends Eloquent {

public function employees()
 {
 return $this-&gt;belongsToMany("Employee")-&gt;orderBy('sortOrder', 'asc');
 }
}
</code></pre>

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

<pre><code>$board = Group::find(6)-&gt;employees;
</code></pre>

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

How to sort NULL values last using Eloquent in Laravel

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

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

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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