Query->first() returns same entity

#1

hi,

I am using Cake4. When using the same query, the first entities are linked (as shown in example below). Any changes done to the entity will be reflected on the other.

I was expecting the query to be re-usable and entities created to be separate (new instance). The only way to do this is for me to create a new query.

Can someone point me to the docs which explains this ?

$query = $this->Articles->find();
$first = $query->first();
// echo $first->title outputs : 'Original Title'
$first->title = 'Modified Title';

// $firstAgain->title is 'Modified Title'. Was expecting 'Original Title'
$firstAgain = $query->first();
#2

Does disableBufferedResults give you your expected behaviour?

#3

When I run query the second time, it returns null. Based on my first post, am I wrong to say the query can be re-used ?

$query = $this->Articles->find();
$query->disableBufferedResults();
$first = $query->first();
$first->title = 'Modified Title';
debug( $first );

$firstAgain = $query->first();
debug( $firstAgain );  // Outputs 'null'
#4

I’m afraid I can’t say. I would have thought that it could, but I haven’t ever needed to do something like this. I’ve turned off buffered results in other scenarios, to keep memory usage down when looping through a large result set, and thought it made sense for this too, but maybe not.

#5

I looked into this surprising behavior.

After use, the Query is marked as executed (debug the query to see this). At this point it, seems it will return a buffered result if one exists or null.

I can’t find an easy way to reset the executed state (or to find that property!) although I do believe queries are reusable… just not in this precise way. More on this idea later.

One reuse strategy

This will do what you are looking for

        $q = $this->Orders->find();
        
        $a = (clone($q))->first();

        $b = (clone($q))->first();

        debug($a==$b); // true
        debug($a===$b); // false

Another reuse strategy?

But on the subject of query reuse, I’ve been casually poking into this for a while since listening to video on prepared statements (which I’ve lost track of).

Examining a cake query, you can see the prepared query with placeholders for values. Here is an example:

SELECT 
  Orders.id AS Orders__id, 
  Orders.order_number 
AS Orders__order_number 
FROM orders Orders 
WHERE id = :c0

The query also contains a map of values:

'params' => [
  ':c0' => [
    'value' => (int) 1,
    'type' => 'integer', 
    'placeholder' => 'c0',
  ],
]

Provocative. Also available on the Query object is an instance of the ValueBinder class with several interesting methods:

Screen Shot 2021-06-17 at 9.17.12 AM
Screen Shot 2021-06-17 at 9.17.12 AM1702×618 112 KB

I haven’t figured out how to use these tools. But the clear implication is that queries can be reused and that there is a way to feed simple replacement values to a prepared query object.

#7

I took another look at this and found how to reuse the query:

        $q = $this->Orders->find();
        $q->disableBufferedResults();
        
        $a = $q->first();
        
        $q->clearResult(); //this resets the query
        $b = $q->first();
        
        debug($a==$b); //true
        debug($a===$b); //false
#8

Based on this quote from query-builder docs :

It also means that if a Query hasn’t been evaluated, no SQL is ever sent to the database. Once executed, modifying and re-evaluating a query will result in additional SQL being run.

I tested using ResultSet and the results are the same, where both instances returned from query are the same instances (referenced to each-other). The only way to get a totally separate instance is to modify the query (e.g. $query->limit(2)).

$query = $this->Articles->find()->limit(2);
$query->cache(false);
$resultSet = $query->all();
$resultSet->each(function($value,$key) {
	$value->title = 'Modified Post';
	// debug(spl_object_hash( $value ));
});

// $query->limit(2);		// This will create a separate instance

$resultSet2 = $query->all();
$resultSet2->each(function($value,$key) {
	// Will show title = 'Modified Post' instead of 'Original Post'
	debug( $value->title );
	//  debug(spl_object_hash( $value ));
});