CakePHP 3原始SQL查询 [英] CakePHP 3 Raw SQL Query

问题描述

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

我需要的方法允许我在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`;; 
 
 //运行这个原始SQL查询我应该使用什么方法？ query（）does not work .. 
 // $ result = $ aumTable-> query（$ sql）; ?? 
 // $ result = $ aumTable-> sql（$ sql）; ?? 
  

如果您可以提供CakePHP 3模型文档的链接，我可以找到此信息，太。我尝试在google上搜索，但只能找到与CakePHP 2有关的问题。

解决方案

首先，你需要得到你的连接：

  //使用Cake\Datasource\ConnectionManager; 
 $ conn = ConnectionManager :: get（'my_connection'）; 
  

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

之后，您可以运行如下所示的自定义查询：

  $ stmt = $ conn-> execute（
'UPDATE posts SET published =？WHERE id =？'，
 [1，2] 
）; 
  

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

然后你就可以这样获取行：

  //读取一行。 
 $ row = $ stmt-> fetch（'assoc'）; 
 
 //读取所有行。 
 $ rows = $ stmt-> fetchAll（'assoc'）; 
 
 //通过迭代读取行。 
 foreach（$ rows as $ row）{
 // Do work 
} 
  

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

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.

解决方案

First you need to get your connection like so:

// use Cake\Datasource\ConnectionManager;
$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

这篇关于CakePHP 3原始SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！