php CakePHP 3 原始 SQL 查询

CakePHP 3 Raw SQL Query

提问by Ananth

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..")).

我正在使用 CakePHP 3，我需要在多个表上运行原始 SQL 查询。在 CakePHP 2 中，这可以通过在任何模型 ( $this->Messages->query("select.."))上使用 query() 方法来完成。

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

我需要允许我在 CakePHP 3 中运行 SQL 查询的方法。以下是我正在使用的代码片段：

$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.

如果您可以提供指向 CakePHP 3 模型文档的链接，我可以在其中找到此信息，那也会很有帮助。我尝试在 google 上搜索，但只能找到与 CakePHP 2 相关的问题。

回答by Tijme

First you need to add the ConnectionManager:

首先你需要添加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

更多信息：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

更多信息：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

更多信息：http: //book.cakephp.org/3.0/en/orm/database-basics.html#executing-fetching-rows

回答by Tobias Gaertner

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

文档在这里：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

1.您需要添加

use Cake\Datasource\ConnectionManager;

2.init the ConnectionManager (as mentioned above)

2.初始化ConnectionManager（如上所述）

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

3.Execute your SQL with something like this

3.用这样的东西执行你的SQL

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

回答by chriss

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.

这个问题已经很老了，但我仍然经常找到它。这是 CAKEPHP 3.6 和（短）新 PHP 版本的解决方案。

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 ().

没有必要使用 ConnectionManager get 函数，而且通常没有意义，因为可能根本不知道连接名称。每个表都有自己的 / 一个连接，可以使用 getConnection() 获得该连接。

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

如果您已经在消息表 ( src/Model/Table/MessagesTable.php) 中，您可以简单地使用连接

$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

如果您不在那里（您的代码建议使用TableRegistry::get()，您也可以使用此表执行此操作

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

then you can execute a RAW query as shown above:

然后您可以执行如上所示的 RAW 查询：

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