CODEWITHSUNDEEP
mysqlsqllaraveleloquent
Enea74
Enea74

Reputation: 321

Sorting a query result using nested clauses

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.

Upvotes: 2

Views: 84

Answers (1)

Qirel
Qirel

Reputation: 26450

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

Upvotes: 2

Related Questions