lundi 28 septembre 2020

Multiple groupBy with date in Laravel?

How can I achieve this output (image below).

enter image description here

This is my Types Table.

enter image description here

and this is my Accounts Table.

enter image description here

This is my Type Model.

public function accounts()
{
    return $this->hasMany('App\Accounts');
}

This is my Account Model.

public function type()
{
    return $this->belongsTo('App\Type', 'type_id');
}

What I want to do is, I want to group the accounts table by types and by month, and then I want to add all amount on each month, and display to the table using DataTables.

I tried this code, but it's not giving the expected output.

$query = Accounts::with('type')->whereYear('created_at', $selectedYear)->select('type_id',
    DB::raw('type_id as type_id'), 
    DB::raw('sum(amount) as total_amount'),
    DB::raw("DATE_FORMAT(created_at,'%m') as months")
)->groupBy('months')->get();

I'm still stuck on this part,

Thanks in advance



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire