laravel eloquent order by specific value with PostreSQL database

Question

I am new in PostgreSQL with Laravel. I have a query that I want to order by status column specific value, not alphabetical order, e.g:

1. pending
2. accepted
3. delivered
4. rejected

Before I used MySQL and it works fine. After changed database to PostgreSQL, query is not working: Here is my code:

$query->orderByRaw('FIELD(status, "pending", "accepted", "delivered", "rejected") ASC')->get();

And error screenshot:

It shows pending in not table column;

Should it be different that query in laravel postgres? Or am I doing something wrong?

https://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql — Pradeep, Aug 13 '21 at 19:27

score 1 · Accepted Answer · answered Aug 13 '21 at 20:02

1

You have to change your FIELD(...) to:

CASE
    WHEN status='pending' THEN 1
    WHEN status='accepted' THEN 2
    WHEN status='delivered' THEN 3
    WHEN status='rejected' THEN 4
END

answered Aug 13 '21 at 20:02

PeterPkp123

1 Answers1