您首先要拥有的是分页查询的确切约束.由于我们要获取除当前页面之外的其他分页记录子集，因此您需要确保两个查询的结果都在相同的顺序中.重复使用查询构建器对象会有所帮助(调整以匹配您的实际分页查询):$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.然后，获取分页的结果集.这将执行两个查询:一个查询记录总数，第二个查询特定页面的事务.$paginatedTransactions = $baseQuery->paginate(100);从这里，我们可以确定检索先前余额所需的记录.返回的分页对象是LengthAwarePaginator的实例，该实例知道总共有多少条记录，页数，其当前在哪个页面上，等等.使用这些信息，我们只是做一些数学运算以获取所需的记录数:total records needed = (current page - 1) * records per page假设用户位于第5页，他们将看到记录401-500，因此我们需要检索之前的400条记录.// 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();现在我们有了数据集中但当前页面之前发生的交易数量，我们可以再次使用基本查询来检索和计算总和:$previousBalance = $baseQuery->limit($limit)->sum('amount'); 在此处添加一个亮点以说明使用数据库执行SUM计算将具有很大的性能优势，而不是在PHP中循环执行.尽可能多地利用数据库！将此余额添加到原始的昨天"余额中，对于分页交易，您应该有一个准确的期初余额. 注意:理论上所有伪编码的内容都可能需要调整.如有疑问或问题，很高兴进行修改. 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 Balance1 100.00 0.00 100.002 10.00 0.00 110.003 0.00 70.00 40.004 5.00 0.00 45.005 0.00 60.00 -15.006 20.00 0.00 5.00It 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. 解决方案 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 pageAssuming 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. 这篇关于重复输入会计分页问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！
