Add `orderByPriority` Method for Custom Sorting in Query Builder

[alirezadp10]

Explanation:

This commit introduces the orderByPriority method to Laravel's query builder, enabling developers to sort query results based on a custom priority array. For example, you can now order records by specific IDs in a preferred sequence like this:

Model::whereIn('id', [2, 4, 6, 1, 3, 5])->orderByPriority('id', [2, 4, 6, 1, 3, 5])->get();

This method leverages SQL's FIELD() function, allowing developers to easily display results in a user-defined order rather than the default ascending or descending order.

Impact:

This feature greatly enhances the flexibility of data retrieval, particularly when a specific order of items is crucial for the application's logic or user interface.

[github-actions[bot]]

Thanks for submitting a PR!

Note that draft PR's are not reviewed. If you would like a review, please mark your pull request as ready for review in the GitHub user interface.

Pull requests that are abandoned in draft may be closed due to inactivity.

[crynobone]

From my previous research this does only work on MySQL and probably PostgresSQL?

[rodrigopedra]

AFAIK, it is MySQL only. Just searched PostgreSQL docs and couldn't find anything similar.

Furthermore, it seems weird to me to have this directly on the query builder, without delegating to the corresponding builder grammar.

If added to the grammar we could throw an exception for the ones which do not support it, similar to what is done for lateral joins.

[innocenzi]

@rodrigopedra AFAIK, it is MySQL only. Just searched PostgreSQL docs and couldn't find anything similar.

Correct. Last time I had to do this using PostgreSQL, I used this workaround:

public function scopeOrderByStatus(Builder $query): Builder
{
    return $query->orderByRaw(<<<SQL
        CASE
            WHEN status = ? THEN 1
            WHEN status = ? THEN 2
            WHEN status = ? THEN 3
            WHEN status = ? THEN 4
        END
    SQL, [
        AircraftStatus::OPERATING,
        AircraftStatus::MAINTENANCE,
        AircraftStatus::UNSCHEDULED_MAINTENANCE,
        AircraftStatus::OPERATIONAL,
    ]);
}

[Mushood]

I have applied the feedback of @rodrigopedra and @innocenzi in this PR #52535

[Mushood]

@alirezadp10 since you are at the base of my PR #52535 , would love give you access to my fork. Otherwise, I don't mind making my changes to your fork I am still thinking on how optimize this feature given that there could be performance issues for large arrays of the priority array.