CakePHP parsing query results alternative

Question

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 nested 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.

What does "A LOT" mean translated into numbers? 1 kilobyte? 5? 10 megabytes? 1 gigabyte? Parsing - how? Into an object, using json_encode, trying to create XML or HTML? You *have* to be specific. — Michael J.V., May 18 '11 at 15:54
If the query is returning too many results, shouldn't you modify the query to return only what you need instead? — JJJ, May 18 '11 at 16:01
The query is returning what I need.... what I need is for CakePHP to not try to parse it and just return my results... — Ben, May 18 '11 at 18:44

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

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!

edited May 23 '17 at 12:04

Community

1
1

answered May 18 '11 at 16:59

pleasedontbelong

19,542
12
53
77

@Webnet - as i said, it has nothing to do with cakephp.. the problem is that you have too many records on the query result and the size of an array is limited by the memory allowed for php... but have you tried this code? – pleasedontbelong May 18 '11 at 20:56
It has everything to do with CakePHP, CakePHP's processing these results into arrays. I don't want that, I want to run a simple loop. Processing these rows wouldn't be an issue with mysql_query(), it's the array processing that's the problem. – Ben May 19 '11 at 01:01
Are you saying that PDO stores the results in something other than a resource? Good observations, thanks for the help! – Ben May 20 '11 at 14:32
PDO can do both http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop – pleasedontbelong May 20 '11 at 14:59

score 0 · Answer 2 · answered May 18 '11 at 19:25

0

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

answered May 18 '11 at 19:25

generalopinion

1,437
2
13
21

score 0 · Answer 3 · answered May 19 '11 at 13:38

0

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.

answered May 19 '11 at 13:38

mentalic

965
2
14
30

CakePHP parsing query results alternative

3 Answers3