Laravel 7: How to show data comparing to nearest date?-CodePudding

I want to show data in my table comparing to the nearest date. There is a table named customers in my database. To view data I used orderBy for schedule column. But it's not solving my problem. I need to view data which is nearest to the date. Example:

id	schedule_date
1	2022-07-25
2	null
3	2022-07-23
4	2022-07-15

Today date is : 2022-07-21. So, I want to show the data from this table like this:

id	schedule_date
4	2022-07-15
3	2022-07-23
1	2022-07-25
2	null

It will show data comparing with today date to nearest schedule date. The date which already passed it will show first, the nearest date will show second and the null schedule_date data will show last.

I tried with this code:

$customer = Customer::where('isDelete', 0)->orderBy('schedule_date', 'asc')->get();

Can anyone please tell me how can I do this?

CodePudding user response：

Customer::orderByRaw('ISNULL(schedule_date), schedule_date ASC')->get();

CodePudding user response：

You can use CASE Statement

    $date = '2022-07-21';
    $customer = Customer::where('isDelete', 0)
    ->orderByRaw('CASE WHEN `schedule_date` < ? THEN 2 WHEN `schedule_date` => ? THEN 1 ELSE 0 END DESC', [$date, $date])->get()