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
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();
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];
});