Eloquent Postgres NULLS LAST

There are lots of answers out there about how to have NULLs returned last in a query on MYSQL. However, Postgres not so much.... According to the PGSQL docs:

The NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise. In other words, you query can be something like

select "frequent_flyer_number" from "frequent_flyer_programmes" order by "frequent_flyer_number" desc NULLS LAST

As far are I can see the only way to add the NULLS LAST in eloquent is through a RAW statement...

$builder->select('frequent_flyer_number')->orderByRaw('frequent_flyer_number desc NULLS LAST')

Not really too much of a problem until you start to use SubSelects in your query scopes

public function scopeOrderByFrequentFlyerNumber(Builder $builder, string $sortDir = 'desc'): Builder
    {
    return $builder
            ->orderBy(FrequentFlyerProgramme::select('frequent_flyer_number')
                ->whereColumn('user_id', 'users.user_id')
                ->orderByRaw(''frequent_flyer_number desc NULLS LAST')
                ->take(1), $sortDir
            );
    }

Other than making the whole scope a RAW query, is there any way to append NULLS LAST to the outer OrderBy?

LaryAI

1 year ago

Level 58

To achieve the NULLS LAST ordering in Eloquent without resorting to raw SQL for the entire query, you can use the orderByRaw method in combination with your subquery. Here's how you can modify your scope to include NULLS LAST:

public function scopeOrderByFrequentFlyerNumber(Builder $builder, string $sortDir = 'desc'): Builder
{
    return $builder->orderByRaw(
        '(SELECT frequent_flyer_number FROM frequent_flyer_programmes WHERE user_id = users.user_id ORDER BY frequent_flyer_number ' . $sortDir . ' NULLS LAST LIMIT 1) ' . $sortDir
    );
}

Explanation:

Subquery with NULLS LAST: The subquery selects the frequent_flyer_number from the frequent_flyer_programmes table where the user_id matches the users.user_id. It orders the results by frequent_flyer_number in the specified direction ($sortDir) and appends NULLS LAST to ensure null values are sorted last.
Outer Query Ordering: The outer query uses orderByRaw to apply the subquery's result as the ordering criterion, ensuring that the NULLS LAST behavior is preserved.

This approach keeps the use of raw SQL to a minimum and integrates it seamlessly with Eloquent's query builder.

Here is the complete scope function:

public function scopeOrderByFrequentFlyerNumber(Builder $builder, string $sortDir = 'desc'): Builder
{
    return $builder->orderByRaw(
        '(SELECT frequent_flyer_number FROM frequent_flyer_programmes WHERE user_id = users.user_id ORDER BY frequent_flyer_number ' . $sortDir . ' NULLS LAST LIMIT 1) ' . $sortDir
    );
}

This should give you the desired ordering with NULLS LAST while still leveraging Eloquent's query builder for the rest of your query.

Please or to participate in this conversation.