I have a situation where I want to order a list taking into consideration an enumeration of strings, where I want the submissions with status==“pending” come first, then with status approved and then with status rejected. I saw an example using SQL with the FIELD function. In my case I have this:
FROM brandbassador submissions
JOIN brandbassador users ON KEYS ‘User::’ || submissions.userId
WHERE submissions.docType = 'Submission’
AND users.docType = 'User’
AND submissions.brandId=$1 AND submissions.network is not missing AND submissions.missionId=$2
ORDER BY FIELD(submissions.status, ‘pending’, ‘approved’, ‘rejected’), submissions.created_at DESC
But it returns the following error:
“ERROR: Wrong number of arguments to function FIELD. - at )”
Any way of doing this type of order by?
Thanks for any help.