Execute plain SQL queries on read replica

1

CakePHP version - 4.6.1

select ORM queries are executing on read replica. But application has multiple plain select SQLs also. These queries are executing on write DB.

How to run plain SQLs on read replica ?

2

What exactly do you mean by “plain SQL queries”

Do you fetch the connection object via the connection manager and execute SQL manually?

Please give an example

3

$sql = “select name from users where id=:id”;
return Hash::combine($this->getConnection()->execute($sql, $arrParams)->fetchAll(‘assoc’)

4

Why are you executing simple queries like that? Why not use ORM methods where you can do exactly what I explained before? I guess you are inside a table class, because otherwise $this->getConnection() doesn’t make sense.

$query = $this->find()->select(['Users.name'])->where(['Users.id' => $id])->useReadRole();
$result = $query->toArray();

But if you insist on doing manual SQL queries you can also do something like

$connection = \Cake\Datasource\ConnectionManager::get('default');
/** @var \Cake\Database\Driver $driver */
$driver = $connection->getDriver(\Cake\Datasource\ConnectionInterface::ROLE_READ);
$statement = $driver->execute($sql, $params, $types);
$result = $statement->fetchAll('assoc');

but then you don’t use any ORM functionality which kind of destroys the purpose of using an ORM.

1 Like
5

@KevinPfeifer Application has multiple manual SQL queries. Currently converting those SQLs to ORM will take time. We will do it later