复式记账（Double Entry Accounting）系统分页严重问题求助

Hey there! Let’s break down how to fix that tricky pagination issue with your double-entry accounting system—this is such a common pain point because running balances depend on every prior transaction, which breaks when you split records across pages.

First, let’s clarify the core problem: When you paginate transactions, each row’s balance isn’t just based on the current page—it’s a cumulative total of every In and Out entry before it. Regular pagination only fetches a subset of records, so you can’t calculate the balance correctly without accounting for all prior entries.

Here are actionable solutions tailored to your Laravel + AngularJS stack:

1. Backend-First Approach (Laravel) #

Calculate the initial balance for the start of the current page, then compute running balances for each record in the page. This keeps the frontend simple and ensures consistency.

Step-by-Step Code Example #

public function getTransactions(Request $request)
{
    $perPage = $request->input('per_page', 10);
    $page = $request->input('page', 1);
    $offset = ($page - 1) * $perPage;

    // 1. Get the initial balance before the current page
    $initialBalance = DB::table('transactions')
        ->orderBy('id')
        ->limit($offset)
        ->select(DB::raw('SUM(in_amount) - SUM(out_amount) as total'))
        ->first()
        ->total ?? 0;

    // 2. Fetch current page transactions
    $transactions = DB::table('transactions')
        ->orderBy('id')
        ->skip($offset)
        ->take($perPage)
        ->get();

    // 3. Calculate running balance for each transaction in the page
    $runningBalance = $initialBalance;
    $transactions->transform(function ($transaction) use (&$runningBalance) {
        $runningBalance += ($transaction->in_amount - $transaction->out_amount);
        $transaction->balance = number_format($runningBalance, 2);
        return $transaction;
    });

    // Return paginated data with balances
    return response()->json([
        'data' => $transactions,
        'initial_balance' => $initialBalance,
        'current_page' => $page,
        'per_page' => $perPage,
        'total' => DB::table('transactions')->count()
    ]);
}

2. Frontend Completion (AngularJS) #

If you prefer to handle the running balance calculation on the frontend (after fetching the initial balance from Laravel), here’s how to implement it:

Controller Code Example #

app.controller('TransactionsCtrl', function($scope, $http) {
    $scope.currentPage = 1;
    $scope.perPage = 10;
    $scope.transactions = [];
    $scope.runningBalance = 0;

    $scope.loadPage = function(pageNum) {
        $scope.currentPage = pageNum;
        $http.get('/api/transactions', {
            params: {
                page: $scope.currentPage,
                per_page: $scope.perPage
            }
        }).then(function(res) {
            $scope.transactions = res.data.data;
            $scope.runningBalance = res.data.initial_balance;
            
            // Optional: If backend doesn't precompute balances, calculate here
            $scope.transactions.forEach(function(txn) {
                $scope.runningBalance += (txn.in_amount - txn.out_amount);
                txn.balance = $scope.runningBalance.toFixed(2);
            });
        });
    };

    // Load first page on init
    $scope.loadPage(1);
});

3. Advanced: Use SQL Window Functions (For Modern Databases) #

If your database supports window functions (MySQL 8+, PostgreSQL, etc.), you can compute cumulative balances directly in the query, then paginate the results. This is efficient for large datasets:

Laravel Query Example #

$transactions = DB::table('transactions')
    ->select([
        'id',
        'in_amount',
        'out_amount',
        DB::raw('SUM(in_amount - out_amount) OVER (ORDER BY id) as balance')
    ])
    ->orderBy('id')
    ->paginate($perPage);

return response()->json($transactions);

Note: This works because the window function calculates the cumulative balance across all records before pagination. Just be mindful of performance with extremely large datasets—adding indexes on id, in_amount, and out_amount will help.

Key Edge Cases to Consider #

• Deleted/Edited Transactions: If transactions can be modified or soft-deleted, always recalculate the initial balance dynamically (don’t cache it long-term) to avoid incorrect balances.
• Non-Sequential IDs: If your id column isn’t continuous (e.g., after deletions), use a subquery to fetch the last ID of the previous page instead of relying on offset directly.
• Currency Precision: Use number_format() or toFixed(2) to ensure balances display correctly with two decimal places.

内容的提问来源于stack exchange，提问作者tinyCoder