Laravel - query the same column with two conditions-CodePudding

I want to get all the datas that were created 30-365 days ago. Tried following codes but it's not working.

Database:

id     created_at

1      2022-05-09   

2      2021-06-08

Here id 2 was created before 365 days from today(2022-06-10), so it should not be shown. However id 1 was created before 30 days but not more than 365 days. So only id 1 should be shown.

Code 1:

$today = Carbon::now();
$doubtfulLoan = Loan::select('*')
->where(function($query) use ($today) {
    return $query
    ->where('created_at', '<', $today->subDays(30)->endOfDay())
    ->where('created_at', '>=', $today->subDays(365)->endOfDay());
 })
->get();

Output: it gives empty array

P.S if the 2nd where clause is commented, it gives both the ids and if the 1st where clause is commented, it gives id 1 only. But keeping both the condition gives empty array. What am I doing wrong?

Code 2:

$today = Carbon::now();
$doubtfulLoan = Loan::select('*')
   ->where([
       ['created_at', '<', $today->subDays(30)->endOfDay()], 
       ['created_at', '>=', $today->subDays(365)->endOfDay()]
   ])->get();

Output: it gives both the array.

Thanks in advance.

CodePudding user response：

use laravel between method here

$today = Carbon::now();
$doubtfulLoan = Loan::select('*')
 ->whereBetween('created_at',[
     $today->subDays(365)->startOfDay(),
     $today->subDays(30)->endOfDay()
 ])->get();

CodePudding user response：

You need to get into the habit of using CarbonImmutable to prevent nasty surprises like this. Use this code:

$today = CarbonImmutable::now();
$doubtfulLoan = Loan::select('*')
->where(function($query) use ($today) {
    return $query
    ->where('created_at', '<', $today->subDays(30)->endOfDay())
    ->where('created_at', '>=', $today->subDays(365)->endOfDay());
 })
->get();

This is because you are doing $today->subDays(30)->endOfDay() which changes the instance value of the carbon instance and then doing $today->subDays(365)->endOfDay() which changes it again. This is however the same instance, so the query builder will do:

SELECT * FROM loans WHERE created_at < '395 days ago' and created_at >= '395 days ago'

since you have passed the same instance. This obviously is never satisfied.