Using temporary tables with Agile Data / UI


Does anyone use temporary tables with Agile Data? I can’t seem to figure out how to get them going.

I have an application that reports on data in a large database, summarizing it in a table in multiple ways. My first cut at it was to simply use expressions in the Agile Data model classes I created and throw them up in a Grid from Agile UI, and that worked – but it was painfully slow when the user selected a data range with a lot of data. Every time I sorted the table on a different column, for example, it would still be re-calculating the summary via queries into the database.

My proposed solution was to simply use a temporary table. I segregated the calculation into a callback routine, using something like:

$f->onSubmit(function ($f) use ($summarytable, $app) {

summarytable is the Grid element I’m using to display the data.

During debugging (not using temporary tables), everything worked fine. However, once I converted to using temporary tables, putting up the summarytable Grid would tell me that the table didn’t exist. If I put the recalculateSummary function in the main part of the code, things would also work with the temporary table.

I did convert my dbConnect call in my App _construct to:

$this->dbConnect(‘mysql://localhost/db’,‘user’,‘password’, array(PDO::ATTR_PERSISTENT => true));

which should be setting up the connection as persistent, and that’s the only way I connect to the database, so I am certainly using the same credentials all the time. Have I missed something?