I ran out of memory when finding ('all') a large amount of data with CakePHP [Solved]

This is Hase from the development team.

my previous article , I wrote about how to find('all') large amounts of data in CakePHP without causing memory errors. However,
I received a lot of feedback from within the company about the content of that article, so
this time I would like to provide some feedback on the previous article.

So, I would be grateful if you would view my previous article as the ramblings of a novice programmer

current code

$data = $this->Model->find('all'); // Some processing ......

Using query() is a no-no

From the previous article

It seems that retrieving with query() is easier to process than retrieving with find('all').
So, I modified it to use query() to retrieve what was being retrieved using find.

This is incorrect

In the first place, the query method is used when executing complex queries, and
since it requires sanitization and validation when used, it is said that it is best not to use it lightly.
In the first place, using find is easier and allows you to make effective use of the framework's functions, so
there was no need to use the query method at all this time.

It is better to get data by looping with a while statement rather than a for statement.

Last time, we used a for loop to split and retrieve the data

Previous code

// Get the number of data items $count = $this->Model->find('count'); // Get 5000 pieces of data at a time $limit = 5000; // Number of times to loop ({data number of items ÷ number of items retrieved per time} rounded up) $loop = ceil($count / $limit); for ($i = 0; $i < $loop; $i++){ // Offset $offset = $limit * $i; $data = $this->Model->query("select * from hogehoge as limit {$limit} offset {$offset};", $cachequeries = false); // Some processing ... ...... }

There's nothing wrong with this method, but

Actually, I found that using a while statement is a better way to get the results.
With a while statement, there's no need to take a count or calculate the number of loops.
Also, it's better to use find('all') instead of query()...

So I rewrote it using a while statement

Rewritten code

// Retrieve 5000 data items at a time $limit = 5000; $params = array('limit' => $limit, 'offset' => 0); while ($data = $this->Model->find('all', $params)){ // Do some processing $params['offset']+= $limit; }

This is much better than using a for statement because there is no unnecessary processing.
I learned something!

That's it.