問題描述

我使用的是 CakePHP 3，我需要對多個表運(yùn)行原始 SQL 查詢.在 CakePHP 2 中，這可以通過在任何模型( $this->Messages->query("select..") )上使用 query() 方法來完成.

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 中運(yùn)行 SQL 查詢的方法.以下是我使用的代碼片段:

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); ??

如果您能提供指向 CakePHP 3 模型文檔的鏈接，我可以在其中找到此信息，那也會很有幫助.我嘗試在 google 上搜索，但只能找到與 CakePHP 2 相關(guān)的問題.

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.

推薦答案

首先需要添加ConnectionManager:

First you need to add the ConnectionManager:

use CakeDatasourceConnectionManager;

然后你需要像這樣獲得你的連接:

Then you need to get your connection like so:

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

更多信息:http://book.cakephp.org/3.0/en/orm/database-basics.html#creating-connections-at-runtime

之后，您可以像這樣運(yùn)行自定義查詢:

After that you can run a custom query like this:

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

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

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

這篇關(guān)于CakePHP 3 原始 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！