Sorting a query result using nested clauses

Question

I'm using this database table for my Laravel app:

| id | description | status   | timestamp  |
|----|-------------|----------|------------|
| 1  | Lorem ipsum | pending  | 2019-08-01 |
| 2  | Lorem ipsum | approved | 2019-08-01 |
| 3  | Lorem ipsum | pending  | 2019-08-01 |
| 4  | Lorem ipsum | approved | 2019-08-01 |
| 5  | Lorem ipsum | review   | 2019-08-01 |
| 6  | Lorem ipsum | review   | 2019-08-01 |
| 7  | Lorem ipsum | closed   | 2019-08-01 |
| 8  | Lorem ipsum | pending  | 2019-08-01 |
| 9  | Lorem ipsum | approved | 2019-08-01 |
...

The desired ordering logic for this table is:

pending > review > approved > closed

and then after, each subgroup must be ordered by

id, DESC

So the desired result should be:

| id | description | status   | timestamp  |
|----|-------------|----------|------------|
| 8  | Lorem ipsum | pending  | 2019-08-01 |
| 3  | Lorem ipsum | pending  | 2019-08-01 |
| 1  | Lorem ipsum | pending  | 2019-08-01 |
| 6  | Lorem ipsum | review   | 2019-08-01 |
| 5  | Lorem ipsum | review   | 2019-08-01 |
| 9  | Lorem ipsum | approved | 2019-08-01 |
| 4  | Lorem ipsum | approved | 2019-08-01 |
| 2  | Lorem ipsum | approved | 2019-08-01 |
| 7  | Lorem ipsum | closed   | 2019-08-01 |
...

I tried to use this eloquent query:

$posts = Post::orderByRaw( "FIELD(status, 'pending', 'review', 'approved', 'closed')" )
         ->groupBy('id')
         ->paginate(5)
         ->toJson();

However, the ID order is ascending:

| id | description | status   | timestamp  |
|----|-------------|----------|------------|
| 1  | Lorem ipsum | pending  | 2019-08-01 |
| 3  | Lorem ipsum | pending  | 2019-08-01 |
| 8  | Lorem ipsum | pending  | 2019-08-01 |
| 5  | Lorem ipsum | review   | 2019-08-01 |
| 6  | Lorem ipsum | review   | 2019-08-01 |
| 2  | Lorem ipsum | approved | 2019-08-01 |
| 4  | Lorem ipsum | approved | 2019-08-01 |
| 9  | Lorem ipsum | approved | 2019-08-01 |
| 7  | Lorem ipsum | closed   | 2019-08-01 |
...

I can't figure out how to order the rows with the greater id first INSIDE each sub-result grouped by statuses.

Grouping by ID never makes any sense as they are UNIQUE

RiggsFolly
– RiggsFolly

2019-08-08 09:14:28 +00:00
Commented Aug 8, 2019 at 9:14 — RiggsFolly
– RiggsFolly, Commented Aug 8, 2019 at 9:14

Qirel · Accepted Answer · 2019-08-08 09:13:04Z

2

You use groupBy() on the ID instead of orderBy(). You can either add , id DESC in your orderByRaw() method, or add a orderBy('id', 'desc') in place of the groupBy() method.

Using a groupBy('id') in this case makes little sense, as its unique.

$posts = Post::orderByRaw( "FIELD(status, 'pending', 'review', 'approved', 'closed')" )
             ->orderBy('id', 'desc')
             ->paginate(5)
             ->toJson();

answered Aug 8, 2019 at 9:13

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting a query result using nested clauses

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related