I would instead recommend increasing your memory limit for each script in php.ini.

You can get access to the items in the datagrid by accessing it's DataSource property.

Alex,

I did try to increase the memory limit (up to 512M) but with no success - I keep getting the error, right after the DataBind method and before I can iterate over the DataSource array.

Any other ideas?

The plugin simply tries to execute a non-paginated query - the same one that's behind the datagrid. It seems that you just have a ton of records :-). Your options:
1) increase the memory limit further, up to several gig
2) write a SQL query by hand that selects only the columns you care about (remember - QQuery essentially does a select *) and process the results manually, generating the CSV.

I can't guarantee my clients' systems provide that much memory to PHP, so I can't rely on that solution.

thing is, I'm doing this at the datagrid level, so that every datagrid on my system has this feature.

is there a way I can extract the SQL query from the datagrid datasource object? then, I could customize/optimize it and, instead of binding the data, execute it independently from the datagrid.

thanks!

To find out what query is used to populate the data grid you can either turn the profiling on, or simply put a breakpoint on the first line of QDatabaseBase::LogQuery() and examine the $strQuery variable.

cmpscabral - I'm afraid that if you want to have a generic "export any datagrid to CSV" functionality, you cannot get it without the high memory requirements. You will need to code each datagrid exporter separately, like vakopian suggests.

The true way to make this possible is to allow QQuery to select individual columns from the database tables. We started this in ticket 79 (http://trac.qcu.be/projects/qcubed/ticket/79), but this is not yet close to done. If you're invested into getting your clients this generic functionality, you probably want to get that piece finished. We can help you.

vakopian, alex - thanks for your replies.

alex, your suggestion would be the best option but you'd still be instantiating all those objects based on the database result.

wouldn't it be wiser to have an option to just return the database result as an array and do whatever I need with it?

does this make sense? is it possible?

cmpscabral - doing a "lightweight qquery" that returns an array instead of full-fledged objects is definitely an option. In fact, you'll see a discussion on QCodo forums (our sister project) about doing exactly that: http://www.qcodo.com/issues/view.php/42/ (see page 3).

None of this has been implemented just yet, but if you feel up to the challenge, we'll help!

alex,

again, thanks for the useful link - will definitely have a look. I'll let you guys know if I come up with something

all I could come up with (in my limited time frame) was writing a custom query to each one of the datagrids (which is a PITA, because of all the left joins and custom columns the grids already had), perform the query directly against the database and build the excel from there (BTW: I'm using http://phpexcel.codeplex.com/ ).

I tried to intercept the databinding at the datagrid level as well as at the database query level but couldn't figure out how to do it.

cmpscabral,

Instead of doing the query manually, you could try my idea in the last post here: http://trac.qcu.be/projects/qcubed/ticket/270 . It will create objects, but they will only contain the fields you need (the rest will be null), and hopefully they won't be too big. Let me know if you need help with that approach.

-Vartan

vakopian,

yes, I'd need your help :) can't figure out how/where to add your code or how GROUP BY clauses would help me

can you give me a brief explanation/example on how to use your code?

thanks!

Reducing column count can be beneficial, but a better OO approach is going to be is to only instantiate one row at a time. Then the memory overhead of the OO approach is only one object over dealing with the array directly.

So I'd suggest doing a loop over the rows in the manually run query result, and instantiating that one row, and then displaying it. Then on the next loop iteration, it'll overwrite that row in memory.

The ideal approach is to create a single object, and replace its data with the new row's data as needed, but there's no quick and easy way to do so, and really, at that point, accessing the row's properties directly and throwing out OO is often going to be a significant improvement.

cmpscabral,

From your first post I got the impression you were dealing with just one table (no joins), but your later posts seam to indicate otherwise. So, if you do have left joins, and no need for group by's, my approach is probably not going to work there.

But I was thinking of a different idea: why not export the data in pieces: fetch the first 20 rows, convert it to CSV, fetch the next 20 rows, convert it to CSV and concatenate with the previous, etc. You can do this by using QQ and the datagrid (maybe with paginator) and with QDataGridExporterButton (it has an option to export one page at a time). Would this not work?

That's a great idea vakopian, though even better if you don't "concatenate", but rather print the CSV as you generate it. Then you only have 20 records worth of anything in memory at one given moment.

Just beware that ExpandAsArray does not play nice with limits.

I too think it's a fantastic idea. It's a tradeoff of performance vs memory footprint; I can even imagine a parameter for the Datagrid Exporter control (how many records to get at once - with the default value of -1 meaning "all records"). I think this can be very nicely retrofitted into the QDataGridExporterButton!

> That's a great idea vakopian, though even better if you don't "concatenate", but rather print the CSV as you generate it.

Right, I was not thinking of concatenating in memory, but rather just saving directly to the disk on the server, piece-by-piece and concatenating as you go, and at the end give a link for download to the user or something.

But if the data has to be displayed, then either paging or some kind of a lazy grid (fetch-the-data-as-you-scroll) approach might be worth a shot.

nice idea - I'll try it and let you know the results, thanks for all the input!