[SOLVED] Where Year In list of years Laravel

Where Year In list of years Laravel

What I want to do is something like this :


$listOfYears = Invoice::pluck('year') ; // [ 2001, 2002, 2003 ]
$products = Product::whereYearIn('purchase_date',$listOfYears)->get();

the field purchase_date is type date so you can't just use whereIn, you need to apply whereYear 1st, then whereIn.
Obviously this won't work because whereYearIn doesn't exist ...
I did this in the most slow & ugly way possible :


$listOfYears = Invoice::pluck('year') ; // [ 2001, 2002, 2003 ]
$products = Product::all()->filter(function($prod) {
     return in_array( date('Y',strtotime($prod->purchase_date)) , $listOfYears );
});

So I am asking if there is a way to achieve this ? Also if the years could be preloaded in the same query with the method with it would be very optimized too.

Note: This could be done easily in MySQL directly

SELECT * FROM products 
WHERE YEAR('purchase_date') in ( SELECT year FROM invoices ) ;

Solution

You can make a scope on Product model like this:

public function scopeWhereYearIn($query, array $years)
{
    foreach ($years as $year) {
        $query->orWhereYear('purchase_date', $year);
    }
}

Then use it as: Product::whereYearIn($years)->get();

See here more about scopes on docs.