Converting collection to Builder is not giving data.
I have two different table called 'invoices' and 'receipts'. So, I just want to show the both records as a ledger sheet with closing balance amount as last column in the table. I am trying to do this using below. But, it is showing no records found in the table. But, we can see the records in while doing print (dd). Am I doing woring apprroach here. Is there any better way to do this.
protected function getTableQuery(): Builder
{
$invoices = Invoice::select(
DB::raw("'invoice' as type"),
'invoices.created_at as date',
'invoices.invoice_amount as amount'
)
->where('invoice_to', Session::get('current_franchisee'))
->orderBy('date');
$receipts = Receipt::select(
DB::raw("'receipt' as type"),
'receipts.created_at as date',
'receipts.amount as amount'
)
->where('received_from', Session::get('current_franchisee'))
->orderBy('date');
$transactions = $invoices->union($receipts)->get();
$closing_balance = 0;
foreach ($transactions as $k => $transaction) {
$closing_balance = ($transaction->type === 'invoice' ? $transaction->amount + $closing_balance : $closing_balance - $transaction->amount);
$transactions[$k]->closing_balance = $closing_balance;
}
return $transactions->toQuery();
}
protected function getTableColumns(): array
{
return [
TextColumn::make('type'),
TextColumn::make('amount'),
TextColumn::make('closing_balance'),
];
}
6 Replies
Hi all, Can anyone guide me here to come out this situation? Thanks in advance.
Do you think you can move this query to a SQL View? I think it would simplify things here in your Filament Table.
@pboivin Thank you for your response. Let me try. It will be great if you can help me by giving sample code or any reference . Thanks again.
Actually, I am planning to create one more table (outstanding) to calculate the current outstanding whenever creating invoice record and receipt record. So that I can create 'closing_balance' field in both invoice and receipt table to update the calculated value on each creation.
Please suggest me if we have any better idea.
I'm not sure I have a better idea. Have a look at this blog post, it explains how to work with SQL views in Laravel :
https://stitcher.io/blog/eloquent-mysql-views
Eloquent MySQL views - stitcher.io
MySQL views are a powerful tool and can be seamlessly integrated with Laravel Eloquent models.
There's nothing special on the Filament side. Build your resource around the Eloquent Model as usual.
Thank you. Let me check.