Retrieve all rows from the last possible 3 years (Laravel + Eloquent)

I’m trying to get all the rows I can in a Model, however it must be the last 3 possible years, as if I have a result in the year 2020 but nothing in the year 2019 then it must search in 2018, 2017, but if I had a result in 2019 then it must search only for the results in 2018.

I’ve already gotten the hang of whereBetween, however I don’t get any results from the database using the following query:

$year = $request->all();
$fromDate = $year['yearFrom'];
$toDate = $year['yearTo'];

return Tipico::whereBetween(
    DB::raw('date(created_at)'), [$fromDate, $toDate]
)
    ->get();

As you can see, I get the year from a post method which gives me the initial and finish year, however it doesn’t validate the information I’m searching for, whats the best way to do it?

Answer

Use Carbon to create your dates:

$fromDate = Carbon::createFromDate($year['yearFrom'], 1, 1)->startOfDay();
$toDate = Carbon::createFromDate($year['yearTo'], 12, 31)->endOfDay();

Then in your query, created_at is already a datetime, so:

return Tipico::whereBetween('created_at', [$fromDate, $toDate])->get();

Remember to include Carbon in your Class at the top:

use IlluminateSupportCarbon;

or only

use CarbonCarbon;

To get the data from the past 3 years, one option would be to run a query to see what years have the data you need.

So, let’s use the yearTo as reference to his and select the top 3 years with data:

$years = Tipico::select(DB::raw('YEAR(created_at) as years'))
               ->where('created_at', '<=', $toDate)
               ->distinct()
               ->orderByRaw('YEAR(created_at) DESC')
               ->limit(3)
               ->get();

Now in $years you will have the 3 years. Now you can run the query with this years as parameter:

return Tipico::whereIn(DB::raw('YEAR(created_at)'), $years)->get();

*Not sure if you can pass $years directly or need to transform to array. Take a look.