如何在Laravel中使用Enloquent来最后排序NULL值 [英] How to sort NULL values last using Eloquent in Laravel

问题描述

我的员工和团体表之间有很多关系。我创建了数据透视表，所有这些都是正确的。但是，我在employee表上有一个sortOrder列，用于确定它们显示的顺序。在SortOrder列中的值为1的Employee应该是第一个，值2应该是第二个，所以。 （或者如果按顺序排列，则返回）sortOrder列是允许空值的整数列。

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

我的组模型：

  class Group扩展Eloquent {
 
 public function employees（）
 {
 return $ this-> belongsToMany（Employee） - > orderBy（'sortOrder'，'asc'）; 
} 
} 
  

这是我在控制器中用来访问我的模型：

  $ board = Group :: find（6） - > 
  

最近在Laravel中排序NULL值的技巧是什么？

解决方案

  public function employees（）
 {
 return $ this 
  - > hasMany 'Employee'）
  - > select（['*'，DB :: raw（'IF（`sortOrder` IS NOT NULL，`sortOrder`，1000000）'sortOrder`'）]）
 - > orderBy（'sortOrder'，'asc'）; 
} 
  

说明：

IF声明解决了这个问题。如果找到NULL值，则会将一些大数字分配给sortOrder。如果发现不是NULL值，则使用实际值。

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:

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?

解决方案

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.

这篇关于如何在Laravel中使用Enloquent来最后排序NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！