flexible search to order by list of values

937 Views

Hi,

I need to write a flexible search query to retrieve order history "order by" order status. I have list of order statuses such as CHECK_VALID, CHECK_FRAUD, READY_TO_PICK, PICKED, SHIPPED, CANCELLED, COMPLETED and few other.
Everything else apart from READY_TO_PICK, PICKED, SHIPPED, COMPLETED, CANCELLED is considered in progress. The query need to return the orders sorted by order status. The inprogress orders should come first and then READY_TO_PICK, PICKED, SHIPPED, COMPLETED, CANCELLED orders should follow.

I am trying to write the query as below. But is not working as expected.

select {pk},{code},{creationTime},{status.code} from {order as o join orderStatus as status on {o.status} = {status.pk}} where {user}='9025141440516' order by {status.code} = 'READY_FOR_PICKUP', {status.code} = 'PICKED_UP', {status.code} = 'SHIPPED' , {status.code} = 'COMPLETED', {status.code} = 'CANCELLED' asc

Can anyone please help.