Laravel Eloquent get data with closest to current date

Hello,

I need help in fetching data which have the closest date concerning the current date, Say, today is 2024-02-27, and in the database, I have many rows with values of 2024-03-28, 2024-02-29, and 2024-04-25. I need to pull rows which have the date 2024-02-29 and once this date passes on means on 2024-03-01, I get data only of 2024-03-28, not others,

I used the below code, but it gives all data,

   public function query(EodFuture $model): QueryBuilder
    {
        return $model->newQuery()->orderByRaw('ABS(DATEDIFF(expiry, \''.Carbon::now()->format('Y-m-d').'\'))')->orderByDesc('last_trade_time');
    }

How to get data which have the closest date to the current date?

krs

2 years ago

Level 17

Hi, I guess you need 2 queries:

The first one to calculate the nearest date: ->orderByRaw('ABS(DATEDIFF(expiry, today()->format("Y-m-d")))')->first()
From this you can extract the date, and with a second query you can get the corresponding records.

As of writing this, I am sure, you can do this in one query... perhaps I can manage that today...

cheers Stefan

1 like

amitsolanki24_

2 years ago

Level 8

@lacoder Try this may be it will work

$model->newQuery()
        ->whereDate('expiry', '>=', now())
        ->orderBy('expiry')
        ->orderBy('last_trade_time', 'DESC')
        ->limit(1);

LaCoder

2 years ago

Level 1

Ok, so it has been resolved with this code,

public function query(EodFuture $model): QueryBuilder
{
    return $model
        ->whereBetween('expiry', [now()->startOfMonth(), now()->endOfMonth()])
        ->where('expiry', '>', now()) // assuming you only want future?
        ->orderBy('expiry')
        ->orderByDesc('last_trade_time');
}

Please or to participate in this conversation.