Double Entry Accounting pagination issue

Question

There is a really serious issue about Double Entry Accounting systems with pagination, I think it is common but I still didn't find any solution for my problem yet.

You can use this link to read about the simple Double Entry Accounting systems just like the one I made with Laravel and AngularJS.

In this system, the expected result (for example) is something like this:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

It is very easy to track the balance inside a cumulative function if you were showing all the transactions in one page, the balance in the last transaction is your current balance at the end of the day.

For example, for a specific range of dates $fromDate->$toDate, we do like:

$balanceYesterday = DB::table('journal')->where('date', '<', $fromDate)
        ->join('transactions','transactions.journal_id','journal.id')->where('transactions.type', "=", 0) /* 0 means the account of the company */
        ->select(DB::raw('SUM(amount) as total_balance'))
        ->first()->total_balance;

Now we have balance from yesterday, we depend on it to calculate the balance after that in a cumulative loop until the end of the process, reaching $toDate;

$currentBalance = $currentBalance + $currentTransaction->amount;
$currentTransactionBalance = $currentBalance;

Now the real problem starts when you have a big amount of transactions, and you need to paginate them $journal = $journal->paginate(100);, let's say 100 transactions per page, the system will work as expected for the first page, as we already can calculate the $balanceYesterday and depend on it to calculate the new balance after every transaction to the end of the 100 transactions in the first page.

Next page will have the problem that it doesn't know what was the last balance at the previous transaction in the first page, so it will start again from $balanceYesterday, making the whole table have wrong calculations.

What I did first to fix, was transferring the last transaction amount (in front-end) to the next page as a parameter, and use it as a starting amount to calculate again, and that was the best solution I had as I was using only << PREV and NEXT >> buttons, so it was very easy to fix it like that.

But I lately found out that this workaround will not work if I have a pagination with page numbers, as the user would like to go through pages to explore the journal, now it is impossible to know the last balance at a specific page, and the system will show wrong calculations.

What I am trying to do is finding a way to calculate the balance at a specific transaction, weather it was a credit or debit, I'm looking for a way to know how much the balance was after a specific transaction is done in a specific date, I DON'T WANT TO ADD A NEW BALANCE COLUMN AND SAVE THE BALANCE INSIDE IT, THE USER IS DOING A LOT OF MODIFICATIONS AND EDITS TO THE TRANSACTIONS FROM TIME TO TIME AND THAT WILL BREAK EVERYTHING AS A SMALL AMOUNT MODIFICATION WILL AFFECT ALL THE BALANCES AFTER IT, I CAN NOT depend on IDs of transactions in any method because transactions might have different random dates, so there will be no ordering by ID but there might be ordering by other fields like date or account owner or type or whatever..

I've been scratching my head on this for about 4 months, I searched online and found no solutions, I hope after this long explanation that my problem is clear, and I hope somebody can help me with a solution, please..

Thank you.

Answer 1

I believe the only thing you really need at this point is to calculate the sum of all transactions from the beginning of the paginated data set (all records, not just the current page's) until one before the first record displayed on the current page.

You can get this by finding the number of transactions that occurred between the start of your entire data set and the current page's transactions, retrieving them via LIMIT, and adding them up.

The first thing you'll want to have is the exact constraints of your pagination query. Since we want to grab another subset of paginated records besides the current page, you want to be sure the results of both queries are in the same order. Reusing the query builder object can help (adjust to match your actual pagination query):

$baseQuery = DB::table('journal')
    ->join('transactions', 'transactions.journal_id', 'journal.id')
    ->where('date', '>', $fromDate)
    ->where('date', '<', $toDate)
    ->where('transactions.type', "=", 0)
    ->orderBy('date', 'asc');
    // Note that we aren't fetching anything here yet.

Then, fetch the paginated result set. This will perform two queries: one for the total count of records, and a second for the specific page's transactions.

$paginatedTransactions = $baseQuery->paginate(100);

From here, we can determine what records we need to retrieve the previous balance for. The pagination object returned is an instance of LengthAwarePaginator, which knows how many records in total, the number of pages, what current page its on, etc.

Using that information, we just do some math to grab the number of records we need:

total records needed = (current page - 1) * records per page

Assuming the user is on page 5, they will see records 401 - 500, so we need to retrieve the previous 400 records.

// If we're on Page 1, or there are not enough records to
// paginate, we don't need to calculate anything.
if ($paginatedTransactions->onFirstPage() || ! $paginatedTransactions->hasPages()) {
    // Don't need to calculate a previous balance. Exit early here!
}

// Use helper methods from the Paginator to calculate
// the number of previous transactions.
$limit = ($paginatedTransactions->currentPage() - 1) * $paginatedTransactions->perPage();

Now that we have the number of transactions that occurred within our data set but before the current page, we can retrieve and calculate the sum by again utilizing the base query:

$previousBalance = $baseQuery->limit($limit)->sum('amount');

Adding a highlight here to explain that using your database to perform the SUM calculations will be a big performance benefit, rather than doing it in a loop in PHP. Take advantage of the DB as often as you can!

Add this balance to your original "yesterday" balance, and you should have an accurate beginning balance for the paginated transactions.

Note: everything pseudo-coded from theory, may need adjustments. Happy to revise if there are questions or issues.

Answer 2

You should be able to formulate a truth statement for the balance for each record as long as you can tell what the order is to calculate the sum for the balance at each point within that ordered list.

For sure this come with a massive overhead as you need to query the whole table for each record you display, but first of all one must be able to do that. As you've shown in the example, you are as long as you do not paginate.

What you could do for pagination is to pre-calculate the balance for each record and store it in relation to the original record. This would de-normalize your data but with the benefit that creating the pagination is rather straight forward.