Laravel按send_date排序：NULL日期始终置于末尾的实现

Hey! Let's get that sorting working right so your NULL send_date values stay at the bottom where you want them. The issue with your original code is that you're chaining two conflicting orderBy calls—let's simplify and fix it.

The Core Problem with Your Code #

Your original line:

Order::orderBy(\DB::raw('ISNULL(send_date), send_date'), 'asc') ->orderBy('send_date', 'desc')

...is applying two separate sort rules. First you sort by the ISNULL check plus send_date ascending, then immediately override part of that by sorting send_date descending. This breaks the NULL-handling logic entirely.

Solution 1: MySQL-Specific (Using ISNULL()) #

If you're using MySQL, you can handle this in a single clean orderByRaw call:

For Ascending Order (Oldest dates first, NULLs last)

Order::orderByRaw('ISNULL(send_date), send_date ASC')->get();

For Descending Order (Newest dates first, NULLs last)

Order::orderByRaw('ISNULL(send_date), send_date DESC')->get();

How this works: ISNULL(send_date) returns 1 for NULL values and 0 for non-NULL. Sorting this ascending puts all non-NULL rows first, then we sort those rows by send_date in your desired order.

Solution 2: Database-Agnostic (Works for MySQL, PostgreSQL, etc.) #

If you want code that works across multiple databases, use the IS NULL comparison instead of the MySQL-specific ISNULL() function:

Ascending Order

Order::orderBy(\DB::raw('send_date IS NULL'), 'asc')
     ->orderBy('send_date', 'asc')
     ->get();

Descending Order

Order::orderBy(\DB::raw('send_date IS NULL'), 'asc')
     ->orderBy('send_date', 'desc')
     ->get();

Here, send_date IS NULL evaluates to a boolean (or 1/0 in MySQL) that we sort ascending, pushing NULLs to the end before sorting the actual date values.

Quick Test to Verify #

Run a quick query to confirm: you'll see all rows with a send_date sorted correctly, followed by every row where send_date is NULL—no exceptions.

内容的提问来源于stack exchange，提问作者angelique000