That's an interesting article that contains some useful information. However, I don't think it directly applies to my problem.
I think that the techniques described in the article are best suited for situations when you have large amounts of complex data that you need to be able to display different views of in a flexible manner. The extracted data is stored back into the database and it is presented to the user in the traditional ways of using find() and passing the info to the [buffered] views. In my case, the data I need to export is already simplified and the query is optimized. I don't need to further simplify it or store it back to the DB. My problem is how to stream this data directly to the client. If I use find() on the entire data, I run into memory issues (mostly because everything is buffered in memory until the data is ready and the overhead of passing the arrays back and forth). If I split the query into multiple pages and call find on subsets of the data, the request becomes too slow (the times of the paged queries add up pretty quickly). I need to execute the query and then iterate over the raw result set and stream/flush the data. I know I can do this all manually but I was hoping I could leverage cake's models and query building abilities instead of doing it all from scratch. Regards - barduck On Mar 9, 5:49 pm, Jorge Horacio Cué Cantú <jorge....@gmail.com> wrote: > Hello, > > Why don't You take a look at the article "Star schema (OLAP) setup for > reporting" at the backery. > It might be used for exporting instead reporting. > > The link to the article is > > http://bakery.cakephp.org/articles/view/starschema-olap-setup-for-rep... > > Please let us know your insights. > Regards. > > On Mon, Mar 8, 2010 at 1:45 PM, barduck <vd_goo...@klunsk.com> wrote: > > > Hi, > > > In my cake application (1.2, apache, mysql), I need to export a large > > amount of data (tens of thousands of records or more) in XML format. > > The query itself is heavy but nothing the application/database > > shouldn't handle reasonably (it takes ~1-2 seconds to complete the > > query itself). > > > I first tried the naive way of just calling find() and passing the > > result to an XML view. This, of course, chokes the application pretty > > quickly and PHP runs out of memory. > > > Next I tried "paging" the query internally within one call in my > > controller, rending each chuck and flushing the output buffer > > manually. This worked better but it is taking way too long since I now > > have dozens of queries instead of one, each taking over a second to > > complete. > > > Now I know there must be a way to achieve this since running the same > > query in phpmyadmin and exporting the result as XML works very well. I > > guess that in this case I need to somehow bypass some of cake > > mechanisms, iterate over the result set myself and steam the data to > > the client. > > > But I still want to use some of cake's strengths when possible so I > > figured I need to do something like this. > > > * use cake for the request: routes, controller/action, parse input > > parameters > > * use cake models to generate the find query > > * obtain the SQL query from cake > > * get the active mysql database connection from cake > > * execute the query on the raw connection > > * iterate over the result set, generate the XML elements, sending > > chucks of data to the client and flushing as needed. > > > (when the above works I may save the XML to intermediate file for > > caching and redirect to that file, using cake's cache to control > > expiration). > > > Does the above make sense or is there any better way to achieve my > > goal? > > > How do I get the raw SQL query for my models without executing it? > > How do I get the database connection? > > > Any help is appreciated. > > > Thanks! > > > - barduck Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
