Level 48
Hi @bhhussain,
Something like this?
->groupBy(DB::raw("DATE_FORMAT(job_invoice_date, '%Y-%m-%d')")
?
Jul 18, 2020
6
Level 2
Group By Date in Controller
Hi,
I am trying to sum the invoice total by grouping the invoice date but it is showing based on date with time
public function index()
{
$arr['jobcard'] = Jobcard::where('job_comp_code', auth()->user()->company)
->where('job_status_name', 'Invoiced')
->selectRaw('job_invoice_date,sum(job_invoice_amount) as total')
->groupBy('job_invoice_date')
->get();
return view('job.jobinvtotal.index')->with($arr);
}
Can anyone please help me to fix it. I already tried in google and spend some time, a different approach is giving different output but I did not get the one I want.
Thank you
It is giving the output like this but it should group the only date
Level 75
public function index()
{
$jobcard = Jobcard::selectRaw('date(job_invoice_date) as date, sum(job_invoice_amount) as total')
->where('job_comp_code', auth()->user()->company)
->where('job_status_name', 'Invoiced')
->groupBy('date')
->get();
return view('job.jobinvtotal.index', compact('jobcard'));
}
Level 2
Thank you very much for your reply @michaloravec and @mvd
@michaloravec : It is grouping the values but it is not showing the date in the view..
@mvd : I am getting the below error when I try your code
stripos() expects parameter 1 to be string, object given
Level 2
If I add the date format in view like below then it is showing 1970-01-01 for all the rows
<td>{{ date('Y-m-d', strtotime($c->job_invoice_date)) }}</td>
<td>{{ number_format($c->total,3) }}</td>
Level 75
@bhhussain When you use this
public function index()
{
$jobcard = Jobcard::selectRaw('date(job_invoice_date) as date, sum(job_invoice_amount) as total')
->where('job_comp_code', auth()->user()->company)
->where('job_status_name', 'Invoiced')
->groupBy('date')
->get();
return view('job.jobinvtotal.index', compact('jobcard'));
}
In the view you have to use date instead of job_invoice_date
<td>{{ $c->date }}</td>
<td>{{ number_format($c->total, 3) }}</td>
Level 2
@michaloravec Thank you very much for your great help. It is working fine.
Please or to participate in this conversation.