Grouping query and then displaying on blade

Hi.

Im at another learning point where i want to try and group an elequent query by user_id and date, but then have it display correctly in blade. Im completely new to grouping and ive tried a few things from the laravel docs and google, but its not quite doing what i want. So hoping the awesome laravel community can help me once again :) ...

I have database table with a lot of rows (which i need to chunk i think from what i read) and each row has an user id, transaction_type and transaction_date.

I want to grab every transaction within the date range that i provide and then group it by the user_id.

I have the groupBy working (bar the chunking), but the next spanner in the works is that there is a transaction type where i need to do a sum of adding all the rows that has a transaction_type of DB and the same for CR and then subtract the 2 from each other.

And then of course i need to display this on a view as User x has a balance of £xx

The database is unfortunately something ive taken over and im now working to try and breakup to put less strain on the query as the rows will grow (now at 90,000 rows).

Heres my controller ::

        $ledgers = Ledger::select('user_id', 'transaction_amount', 'transaction_date', 'transaction_type')
        ->whereBetween('transaction_date', ['2018-01-01', Carbon::now()->toDateString()])
        ->get()
        ->groupBy(function($date) {
            return Carbon::parse($date->transaction_date)->format('m');
        });

Any help would be great.

Thank you

bobbybouwmann

7 years ago

Level 88

Well after you perform ->get() you will get a collection back. With this collection you can do anything you want. Now in your case you want to group them by type. Based on the documentation you can already perform most actions!

Documentation:

So in your case you can do something like this

 $ledgers = Ledger::select('user_id', 'transaction_amount', 'transaction_date', 'transaction_type')
    ->whereBetween('transaction_date', ['2018-01-01', Carbon::now()->toDateString()])
    ->get();

$transactionsDbSum = $ledgers->where('transaction_type', 'DB')->sum('transaction_amount');

Do you have a specific example that is not working for you?

skoobi

7 years ago

Level 13

Hi thanks for the reply. So what i have currently on the old db is a list of transactions made by each user, each with a credit (CR) or debit (DB) tag assigned to it.

With this i need to grab each users transactions and then use SUM to add the CR together and the same for DB. Once i have them 2 sums i can subtract the DB form the CR to get the total balance for each user individually.

The next thing i need to do though is to get all this from a date range i.e. jan to feb etc.

Hope that makes sence

Please or to participate in this conversation.