how to get unique year and month list from database "date" column (laravel)

Question

here is my code and its working fine.

SubjectReport::selectRaw('YEAR(date) as year, MONTH(date) as month')->distinct()->get();

I want to know is it another way to get data from database using eloquent or collection methods?

example:

2023-01
2023-02
2023-03

Thanks.

Answer 1

If you need to get the data in the desired format right at the database level, I think your approach is the way to do it.

An alternative is using mutators to get the right format (but casting is only applied when converting to array or JSON).

// Add this to your model

protected $casts = [
    'date' => 'date:Y-m'
];

// Get the date with right format

$formattedDates = SubjectReport::select('date')->distinct()->get()->toArray();