Force Model::find() to reuse a prepared statement in CakePHP

Question

In raw PHP I'd do it like this:

$sth = $dbh->prepare("SELECT * FROM tags WHERE tag=:tag");

foreach ( $tags as $tag ) {
    $sth->bindValue(':tag', $tag);
    $sth->execute(); // followed by fetch, etc.
}

In CakePHP I'd have to use the native find() function:

foreach ( $tags as $tag ) {
    $this->Tag->find('first', array(
        'conditions' => array(
            'tag' => $tag,
        ),
    ));
}

I'm pretty sure this will result in CakePHP firing a set of separate database queries which won't utilise the benefits of having the prepared statement in place.

Is there any way I could force CakePHP to do that without modifying the core?

UPDATE There is a way to call $this->getDataSource() directly but I'm looking for a way to keep using the find() method, if possible, because it handles contains automatically and the code for those complex queries using joins and all just looks more elegant that way.

I know this isn't the answer you're looking for, but I'd rather execute one single query, if possible, to fetch all required tags, using `$this->Tag->find('all', array(/* ... */))`. — fquffio, Feb 01 '16 at 15:06

score 1 · Accepted Answer · answered Feb 01 '16 at 15:25

You would need to create a custom/extended datasource, and/or a custom connection class, either could implement an appropriate caching mechanism.

On datasource level, you'd have to re-implement DboSource::_execute(). On connection level you'd have to override PDO::prepare().

Just look at what DboSource::_execute() currently does:

https://github.com/cakephp/.../blob/2.7.9/lib/Cake/Model/Datasource/DboSource.php#L448

It will create a new statement each time it is being invoked, and the only way to hook in without re-implementing the whole method, would be using a custom connection ($this->_connection).

However, in order to be able to use a custom connection class, you'd have to re-implement the connect() method (implemented by the individual DBO driver classes), which isn't a nice thing to do either.

https://github.com/cakephp/.../blob/2.7.9/lib/Cake/Model/Datasource/Database/Mysql.php#L152

See also Cookbook > Models > DataSources

Thank you for the answer. I hoped there would be an easier solution. But I'm sure this will be helpful to whoever chooses to try this, so I'm accepting the answer. Thanks again — ᴍᴇʜᴏᴠ, Mar 25 '16 at 23:23

score 1 · Answer 2 · answered Feb 01 '16 at 15:25

1

Cake is doing what you want already in the DboSource class for you.

Just read the code of it.

answered Feb 01 '16 at 15:25

floriank

25,546
9
42
66

Force Model::find() to reuse a prepared statement in CakePHP

2 Answers2