Phalcon 3.2.4 - ResultSet "losing" the correct results

[Belzebell]

Hi,

I have a piece of code that runs daily and was working fine up until today (no changes were made to it).
Upon investigating, I found the issue lies within a ResultSet I get.

I have a function on my Phalcon model for a DB table, let's call it TestEntries:

public function get_entries_status_three()
    {
        $param = [
            'conditions' => "status = :status:",
            'bind' => [
                'status' => 3
            ],
        ];

        return parent::find($param);
    }

This returns a ResultSet/Simple with 35 entries.
I then loop through them and update the status to 2.

foreach ($test_entries as $test_entry) {
                $test_entry->status = 2;
                $test_entry->save();
            }

I appreciate that my original query (looking for status 3) would now not yield any results as the entries have status 2. This code is NOT running in a db transaction begin/commit block.

However, without any other code inbetween, I know have a second foreach loop through the ResultSet (which still reports a count of 35). It might seem poor coding to have to foreach loops of the same items after each other, but let us please not focus on that for the sake of the argument.

In my second loop, I just add all the IDs to an array.

$my_ids = []
foreach ($test_entries as $test_entry) {
                $my_ids[] = $test_entry->entry_id;
            }

This fails. When I added a var_dump($test_entry) into the loop (instead of adding to array), it output 35 times bool(false).
I understand this is due to the status having changed and the query not finding the result again. In a test run where I skipped updating status to 2 and instead updated a random other field, it works, confirming my suspicion.
However, previously, I am certain it was caching the results. This code has been working for months (it runs daily) and only started failing today. It kept failing during all my re-runs when I was trying to identify the problem.

I guess what I am saying is, I understand what is failing, I just don't understand why it suddenly is!
I'd appreciate any advice or explanation as to why this might "suddenly" not work anymore.

[Belzebell]

I figured out the answer to my question.

The key lies within this part:

This returns a ResultSet/Simple with 35 entries.

As it turns out, the ResultSet logic changes when we have more than 32 results.
We can see this in the ResultSet constructor - if the result count is 32 or less, all results are fetched at once and stored in the ResultSet, meaning we can loop through as often as we want.

However, if we get more than 32 results, none of them are fetched in the constructor, and instead they are fetched every time during the loop, hence why the second loop now fails.

My previous, successful runs of the job had had around 25-30 results, only the last one, where it started failing, had 35.

[niden]

You can also change this behavior by using the orm.resultset_prefetch_records = "32" for your model. If you set that as a "0" (string) for your models, it will not prefetch any records. This change is available on v5.

[m-yamaishi09580]

@niden
Hello.

I am using Phalcon v5.2.1 and am having trouble with the SQL being re-issued every time I use a result set in a loop.

I tried phalcon.orm.resultset_prefetch_records = "32" in php.ini and \Phalcon\Mvc\Model::setup(["prefetchRecords" => "32")
but the behavior did not change.

How can I solve this problem?

[starjackioaz]

In the online communities that I have discovered, the play snake provides users with helpful suggestions for how they might amuse themselves and discover new and exciting things.