Where Year In list of years Laravel

Question

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 ) ;

Answer 1

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.

Answer 2

You can use whereRaw() function in laravel eloquent. In raw query use extarct() part of MySQL it will return the year from date.

$products = Product::whereRaw('extract(year from purchase_date) = ?', $listOfYears)->get();

Answer 3

Try this one

$products = Product::whereRaw("YEAR('purchase_date') in ?", $listOfYears)->get();

Update

$products = Product::whereRaw("YEAR(purchase_date) in ?", $listOfYears)->get();