I've developed a subscription based application built using Laravel5/MySQL. And I'm dealing with fetching invoices from mysql. On every invoice we've to display a few columns i.e. "Previous Balance", "Payments", "This Month's Charge", "Payable By Due Date", "Payable After Due Date". Now the "Previous Balance" is "Payable by Due Date" from last invoice, which in-turn is calculated as "Previous Balance" - "Payments" + "This Month's Charge". So here starts the recursion and will continue until all records in the db are fetched. This too works fine for one single invoice. But when I try to fetch multiple (100~200) server will run out of memory. With a table of ~13k invoices.
As a fix to this problem, I added a new column to the table "payable_by_due_date" at the time of invoice generation update this column. After the change and updating all the records, I tried with ~1000 invoices and it took 3~4 seconds to fetch all the records, make PDF for all the invoices and pack them all in a zip file and handover that zip file to the user for download.
But there is a big problem with this approach, what if in future any of the invoices requires rectification, all of the invoices that were generated after that particular invoice will require manual update.
So, if anyone of you could help me get out of this problem, I'll be thankful.
via Rahul M
