Sort Laravel Collection by custom order, other than asc or desc

Question

I have this array as Laravel Collection :

"data": [
  {
    "id": 863368,
    "reference": "Ref 1",
    "status": 1
  },
  {
    "id": 863391,
    "reference": "Ref 2",
    "status": 2
  },
  {
    "id": 863390,
    "reference": "Ref 3",
    "status": 2
  },
  {
    "id": 863396,
    "reference": "Ref 4",
    "status": 3
  }
];

And I need to sort it by status, not by asc or desc, but the records with status 2 should be first on the list, then status 1 then status 3.

I can't figure out how to achive this, thank you in advance.

can status only be 1,2 or 3? Or there are more than 3 status? — Adarsh Sojitra, Feb 19 '19 at 13:45
i think you need a highly repeated status order .Right? 2(2 times),1,3 — prasanth, Feb 19 '19 at 13:47

SergkeiM · Accepted Answer · 2021-07-22T10:24:01.067

9

Hi had similar issue,

$final = $collection->sortBy(function($item){
    return array_search($item->status, ['2', '1', '3']);
});

['2', '1', '3'] (Status with number 2 will be first because his position key is 0, just change the order as you need.

Or you can create an array like ['2' => 1, '1' => 2, '3' => 3]

use like return $arry_of_order[$item->status]; (faster then calling array_search each time)

P.S. If you can use Database (in case MySQL), as other people here suggested, you should stick to it, $query->orderByRaw("FIELD(status, '2', '1', '3')")

For PostgreSQL look here

edited Jul 22 '21 at 10:24

answered Feb 19 '19 at 13:45

SergkeiM

3,934
6
36
69

2

This solution is good for a small number of collection. If a collection is too large using MySQL's ordering will be better :) – Mihir Bhende Feb 19 '19 at 13:50
1

@MihirBhende The question was about `laravel collection` not DB, but yes ordering from DB will be faster. – SergkeiM Feb 19 '19 at 13:51
2

I agree and your solution is totally correct, just considering the possibility of this collection built from a query, I felt to mention this – Mihir Bhende Feb 19 '19 at 13:54
1

@MihirBhende thank you, I updated my answer, so TS can take this into consideration. – SergkeiM Feb 19 '19 at 13:55

score 4 · Answer 2 · answered Feb 19 '19 at 13:49

4

You can get this by using orderByRaw

Model::orderByRaw('FIELD(status, 2, 1, 3)')->get();

Also By using orderBy

Model::orderBy(DB::raw('FIELD(status, 2, 1, 3))'))->get();

answered Feb 19 '19 at 13:49

Gautam Rai

2,445
2
21
31

I receive and error if I try to use this: lluminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') asc' at line 1 (SQL: select * from `scenarios` order by FIELD(status, 2, 1, 3)) asc) in file /home/vagrant/code/projectresourceonline/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671 – NicuVlad Apr 01 '21 at 06:28
Do you have code that is compatible with Postgres? FIELD is an invalid function – Riza Khan Feb 05 '23 at 04:35

score 0 · Answer 3 · answered Feb 19 '19 at 13:46

0

If you are getting data from MySQL, you can just change ORDER BY like this:

ORDER BY FIELD(status, 2, 1, 3)

answered Feb 19 '19 at 13:46

Andrii Filenko

954
7
17

Sort Laravel Collection by custom order, other than asc or desc

3 Answers3

Linked