Payments table grouped by months with datatables

Hello,

may I ask for help or guidance with creating a datatables page that will show payments grouped by months?

I have two tables: table of clients with primarykey id and full_name as client names. table of payments with primarykey id and client_id as foreignkey, amount (here I store payment amounts), date (here I store manually added payments).

I need to create a datatables page that will retrieve clients->full_name, based on client_id from payments table, sum of the payments amount (if there is more than one payment in a month) and I need to group all these payments by months, where every month will contain the sum of payments.

I followed @PovilasKorop take on a similar topic laracasts . com/discuss/channels/laravel/group-by-month-and-company-sum-count and so far I got this: prnt . sc/z7d8zk

| Client | Apr 21 | Feb 21 | Mar 21 | Jan 21 | |---|---|---|---|---| | 74 | 250 | 0 | 0 | 0 | | 98 | 0 | 200 | 300 | 0 | | 561 | 0 | 0 | 0 | 250 |

Payment Model has

public function clientNames()
    {
        return $this->hasOne('App\Models\Client', 'id', 'client_id');
    }

Payment Controller

$report = [];
        $payments = Payment::select([
            'client_id',
            DB::raw("DATE_FORMAT(date, '%Y-%m') as month"),
            DB::raw('SUM(amount) as amount')
        ])
            ->with('clientNames')
            ->groupBy('client_id')
            ->groupBy('month')
            ->get();

        $payments->each(function($item) use (&$report) {
            $report[$item->month][$item->client_id] = [
                'amount' => $item->amount
            ];
        });

        $clientIds = $payments->pluck('client_id')->sortBy('client_id')->unique();

First, I cant retrieve client names, based on client_id. Next, I need to display it using datatables.

If anyone could help me out with this, would appreciate it very much.