CakePHP 3 Raw SQL Query

Question

I'm using CakePHP 3, I need to run a raw SQL query on multiple tables. In CakePHP 2, this could be done by using the query() method on any model ( $this->Messages->query("select..") ).

I need the method that allows me to run a SQL query in CakePHP 3. Following is the code snippet I'm using:

$aumTable = TableRegistry::get('Messages');
$sql = "SELECT (SELECT COUNT(*) FROM `messages`) AS `Total_Count`,
        (SELECT COUNT(*) FROM `messages_output`) AS `Total_Output_Count`,
        (SELECT COUNT(*) FROM `messages_output` WHERE `is_success`=1) AS `Total_Successful_Output_Count`,
        (SELECT COUNT(*) FROM `messages_output` WHERE `is_success`=0) AS `Total_Error_Output_Count`,
        (SELECT COUNT(*) FROM `users`) AS `Total_User_Count`;";

// to run this raw SQL query what method should i use? query() doesn't work..
// $result = $aumTable->query($sql); ??
// $result = $aumTable->sql($sql); ??

If you can provide links to CakePHP 3 model documentation where I can find this info, that would be helpful too. I tried searching on google but could only find questions related to CakePHP 2.

Answer 1

First you need to add the ConnectionManager:

use Cake\Datasource\ConnectionManager;

Then you need to get your connection like so:

// my_connection is defined in your database config
$conn = ConnectionManager::get('my_connection');

More info: http://book.cakephp.org/3.0/en/orm/database-basics.html#creating-connections-at-runtime

After that you can run a custom query like this:

$stmt = $conn->execute('UPDATE posts SET published = ? WHERE id = ?', [1, 2]);

More info: http://book.cakephp.org/3.0/en/orm/database-basics.html#executing-queries

And then you are ready to fetch the row(s) like this:

// Read one row.
$row = $stmt->fetch('assoc');

// Read all rows.
$rows = $stmt->fetchAll('assoc');

// Read rows through iteration.
foreach ($rows as $row) {
    // Do work
}

More info: http://book.cakephp.org/3.0/en/orm/database-basics.html#executing-fetching-rows

Answer 2

The documentation for this is here: http://book.cakephp.org/3.0/en/orm/database-basics.html#executing-queries

But what's not written there is how to execute it. Because it cost me a while, here is the solution for that:

1.You need to add

use Cake\Datasource\ConnectionManager;

2.init the ConnectionManager (as mentioned above)

$conn = ConnectionManager::get('my_connection');

3.Execute your SQL with something like this

$firstName = $conn->execute('SELECT firstname FROM users WHERE id = 1');

Answer 3

The question is already very old, but I still find it frequently. Here is a solution for CAKEPHP 3.6 and (short) for newer PHP Versions.

It is not necessary to use the ConnectionManager get function and often it does not make sense, as the connection name may not be known at all. Every table has its / a connection which one can get with getConnection ().

If you are already in the Messages Table (src/Model/Table/MessagesTable.php), you can simply use the Connection

$con = $this->Messages->getConnection();

If you are not there (what your code would suggest with TableRegistry::get(), you can do that with this table as well

// $aumTable is declared in question
$con = $aumTable->getConnection();

then you can execute a RAW query as shown above:

$result = $con->execute ();
// short 
$result = $this->Messages->getConnection()->execute ('Select * from ...')
// or ($aumTable is declared in question)
$result = $aumTable->getConnection()->execute ('Select * from ...');