CakePHP parsing query results alternative

I have a query that's returning a LOT of results and my code is running out of memory trying to parse the results... how can I run a query in CakePHP and just get normal results?

By parsing it I mean....

SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id

With the above query it'll return....

array(
   0 => array(
       'table1' => array(
            'field1' => value,
            'field2' => value
       ),
       'table2' => array(
            'field1' => value,
            'field2' => value
       )
   )
)

When it parses those results into nes开发者_C百科ted arrays is when it's running out of memory.... how do I avoid this?

I couldn't hate CakePHP any more than I do right now :-\ If the documentation was decent that would be one thing, but it's not decent and it's functionality is annoying.

you could do:

$list = $this->AnyModel->query("SELECT * FROM big_table");

but i dont think that will solve your problem, because if you have, for exemple, 10millon rows.. php wont be able to manage an array of 10millon values...

but you might want to read this two links to change the execution time and the memory limit.. you could also change them on your php.ini

Good Luck!

EDITED hmm thanks to your question i've learned something :P First of all, we all agree that you're receiving that error because Cake executes the query and tries to store the results in one array but php doesn't support an array that big so it runs out of memory and crashes.. I have never used the classic mysql_query() (i prefer PDO) but after reading the docs, it seems that mysql_query stores the results inside a resource therefore, it's not loading the results on memory, and that allows you to loop the results (like looping though a big file). So now i see the difference... and your question is actually, this one:

Can I stop CakePHP fetching all rows for a query?

=) i understand your frustration with cake, sometimes i also get frustrated with it (could you believe there's no simple way to execute a query with a HAVING clause?? u_U)

Cheers!

I'd suggest you utilize the Containable behavior on your model. This is the easiest way to control the amount of data that's returned. I've confident that this is precisely what you need to implement.

CakePHP :: Containable :: Core Behaviors

You should limit the rows returned from your query (like 500 rows) and allow the user to fetch more rows when needed (next 500 rows at a time). You could do that nicely with the pagination component and a little AJAX.