I have a app where a user can create an order. I want to show him todays orders and then order by normal. Like this:
Todays date: 2024-04-06
2024-04-06 #order1
2024-04-06 #order2
-then sort by normal
2024-04-08 #order4
2024-04-01 #order7
whats going through my head is if I sort by normal then my dates are showed duplicated ? I mean at first is 2024-04-06 then I sort by normal from 2024-04-08 then it goes downward and then I reach again 2024-04-06. So is it possible to sort by today then by rest without showing it duplicated ?
currently this is my code:
SELECT
l.id as location_id,
l.name,
lo.id,
lo.start_time,
lo.end_time,
lo.leader_id,
lo.is_storno,
lo.storno_at,
lo.storno_by,
lo.employees_need,
lo.created_at
FROM location_orders lo
INNER JOIN location l
ON l.id = lo.location_id
WHERE lo.tenant_id = $1 AND lo.deleted_by IS NULL
ORDER BY lo.start_time DESC LIMIT 50;
I think this solution works, I use a conditional expression to put the orders of today on the top. I use a CASE where the today orders get 0 and the rest get 1. With that , the ORDER BY will first order the 0's (today orders) and then 1's (other dates). Hope it helps, if you have any doubt, ask.
SELECT
l.id as location_id,
l.name,
lo.id,
lo.start_time,
lo.end_time,
lo.leader_id,
lo.is_storno,
lo.storno_at,
lo.storno_by,
lo.employees_need,
lo.created_at
FROM location_orders lo
INNER JOIN location l ON l.id = lo.location_id
WHERE lo.tenant_id = $1 AND lo.deleted_by IS NULL
ORDER BY
CASE
WHEN DATE(lo.start_time) = CURRENT_DATE THEN 0
ELSE 1
END,
lo.start_time DESC
LIMIT 50;