How do I simplify Laravel query for date searching by year and grouping by month

Question

Is there a way to simplify? I have the date_billing column selected three times for the entire date, month, and year. Is there a way to do something like ->having('YEAR(date_billing)', '=', $this->year); and the same with month? What I have works, but it's a lot of repeating and I'm sure there is a better way. The below is

->addSelect([
    'date_billing' => Invoice::select(DB::raw('date_billing'))
     ->whereColumn("id", "=", "invoice_line_item.invoice_id"),
       
     'month' => Invoice::select(DB::raw('MONTH(date_billing) month'))
      ->whereColumn("id", "=", "invoice_line_item.invoice_id"),
    
     'year' => Invoice::select(DB::raw('YEAR(date_billing) year'))
      ->whereColumn("id", "=", "invoice_line_item.invoice_id")
])
->having('year' ,'=', $this->year)
->groupBy('month')
->get();

Edit:

I fixed the 'year' by adding ->whereYear()allowing me to take out the 'year' from the addSelect array, but I still have to have the month.

->addSelect([
   'date_billing' => Invoice::select(DB::raw('date_billing'))
   ->whereColumn("id", "=", "invoice_line_item.invoice_id"),
   ->whereYear("date_billing", $this->year),

    'month' => Invoice::select(DB::raw('MONTH(date_billing) month'))
    ->whereColumn("id", "=", "invoice_line_item.invoice_id"),
])

Answer 1

Interesting, I haven't used addSelect or whereColumn before.

This is untested and a guess, if there are other parts to the query builder then it would be helpful to see those.

Are you using a join or leftJoin anywhere?

->addSelect(DB::raw('date_billing, YEAR(date_billing) year'))
->whereColumn('id', 'invoice_line_item.invoice_id')
->having('year', '=', $this->year)
->groupByRaw('MONTH(date_billing)')
->get();