Prepared Statement in Cake5

Need Help
1

In Cake4 I had worked out how to make prepared statements to use in situations where I had to do the same query many times. This was WAY faster than defining the query for each use.

My Cake4 process was:

//Make a template query
$this->query = $this->Orders->find()
    ->where(['Orders.id' => 5])
    ->contain(['Client', 'OrderLines']);

//get a statement object 
$this-statement = $this->Orders->getConnection()->prepare($query);

//Now the heavy lifting is done and the statement has the query as a string
//In that string the conditional values are identified as ':c0', ':c1', etc.

//then, in later code when I know the actual values to use:
$this->statement->bindValue(':c0', $orderId, 'integer');
$this->statement->execute(); //not sure what was happing here

//and I could get my result set
$result = (new ResultSet($this->query, $this->statement))->first();
      debug($r->id);
}

I’ve fumbled my way through the creation of a Statement and the binding of the values in Cake5. I was not able to break creation and binding of the values into separate steps and I’m not thrilled about that…

I can get various array results out of the Statement object but I can’t figure out how to get a ResultSet in Cake5.

This is my Cake5 process so far:

//I make a template query, then set a property to hold the 
//string version of the sql (with :c0, :c1, etc)
$this->queryString = $this->makeAQuery()->sql();

//Then I make my Statement and bind the values in a single step
$statement = $this->Order->getConnection()->execute(
    $this->queryString,
    [':c0' => $orderId],
    [':c0' => 'integer']
);

//$statement->fetchAssoc() and other array getters work
//stuck here
//$result = new ResultSet(?????)

At a minimum I would like to know how to get a ResultSet from my prepared statement.

Ideally, I’d like to get a sketch of how a person who actually understood Cake5 at this level would solve this problem.

2

I am just baffled why you want to do all this “manual” work yourself if instead the ORM can do all the hard work for you…

First of all the CakePHP ORM is SQL injection safe as long as you use it correctly.

E.g. you should not put variables/user input into the LEFT side of a where array.

See Query Builder - 4.x on what you can do and how to do manual value binding if you so desire.

But doing something like

$query = $this->Orders->find()
    ->where(['Orders.id' => $orderId])
    ->contain(['Client', 'OrderLines']);
$result = $query->all()->toArray();

is totally fine and SQL injection safe.

3

In a situation where I want to run the same query many times during a single request, there is a worthwhile speed advantage.

For example, if I’m importing a .csv into a database I would want to check the persisted data to avoid duplicates. Or I might want to distribute the incoming values into different tables.

By using the ORM to create the ‘template’ queries first, then connection->execute()ing the resulting string while binding the new values, I can at least double the speed of execution. For complex queries the speed gain is even greater.