Arrange rows based on given values in Laravel Eloquent

user_8505034 0
Viewed 106

So I have these fields in my directors table

id, name, position, summary, deleted

This table has the following records

 1 | John Doe | President | Some text | 0
 2 | Mary Doe | Vice President | Some text | 0
 3 | Hannah Doe | Treasurer | Some text | 0
 4 | Ann Doe | Board | Some text | 0
 5 | Mark Doe | Board| Some text | 0
 6 | Johnny Doe | Secretary | Some text | 0

As of now, I can get them by using this code

  $directors = Director::where('deleted',0)->get();

But how can I arrange based on my preferred values, this should arrange like this.

President, Vice President, Secretary, Treasurer, Board and if the position is blank this will be below the records of Board Position

 1 | John Doe | President | Some text | 0
 2 | Mary Doe | Vice President | Some text | 0
 6 | Johnny Doe | Secretary | Some text | 0
 3 | Hannah Doe | Treasurer | Some text | 0
 4 | Ann Doe | Board | Some text | 0
 5 | Mark Doe | Board| Some text | 0
laravel mysql php
2 Answers

The query will be like this

SELECT *
FROM `directors`
WHERE `deleted` = '0'
ORDER BY CASE position
             WHEN "President" THEN 1
             WHEN "Vice President" THEN 2
             WHEN "Secretary" THEN 3
             WHEN "Treasurer" THEN 4
             WHEN "Board" THEN 5
             ELSE 6 END, position;

Eloquent version;

return Director::where('deleted', 0)
            ->orderBy(
                DB::raw(
                    'CASE position
                        WHEN "President" THEN 1
                        WHEN "Vice President" THEN 2
                        WHEN "Secretary" THEN 3
                        WHEN "Treasurer" THEN 4
                        WHEN "Board" THEN 5
                        ELSE 6 
                    END, position'
                )
            )
            ->get();
user_21889220

If your database is small enough, and you are not against doing the sort in laravel after the query, as it may get slow with massive data, then here is one way to do it using the collection method sort().

$sortOrder = array_flip(['President', 'Vice President', 'Secretary', 'Treasurer', 'Board', '']);

$directors = Director::where('deleted',0)
              ->get()
              ->sort(function($prev,$next)use($sortOrder){
                  // Note: Flip $prev and $next around on this next line for opposite order
                  return $sortOrder[$prev->position] <=> $sortOrder[$next->position];
                });
user_129228810
Related