php 如何在 PhalconPHP 中运行 RAW SQL 查询

How to run RAW SQL query in PhalconPHP

提问by Sir Z.

I try to get result from this query

我尝试从这个查询中得到结果

$sql = "
                SET @col = NULL;
                SET @sql = NULL;

                Select
                  Group_Concat(Distinct
                    Concat(
                      'SUM(CASE WHEN tbl.sdate = ''',
                      colname,
                      ''' THEN tbl.result ELSE NULL END) AS ''',
                      colname,''''
                    )
                  ) Into @col
                From (
                    select concat(month(i.invdate),'.',year(i.invdate)) as colname
                    from invoices as i
                    where i.invtype = 1 and i.pid = 5
                    order by i.invdate  
                ) As collst;

                SET @sql = CONCAT('SELECT tbl.wrkname,', @col, ' 
                                        FROM (
                                            Select wl.wgname As wrkname, Concat(Month(i.invdate),''.'',Year(i.invdate)) as sdate, Sum(id.qty * id.price) As result
                                            From invoices As i
                                                Join invoicedetails As id
                                                    On i.pchid = id.pchid
                                                Join workgroups As w
                                                    On i.wid = w.wid
                                                Join workgrouplist As wl
                                                    On w.wglid = wl.wglid
                                            Where i.invtype = ', 1, ' And i.pid =', 5,
                                            ' Group By i.pid, sdate
                                            Order By i.invdate, wrkname
                                        ) AS tbl
                                        GROUP BY tbl.wrkname');

                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
                "

But result was "SQLSTATE[HY000]: General error".

但结果是“SQLSTATE[HY000]：一般错误”。

Phalcon version: 1.2.4

法尔康版本：1.2.4

Profiler log is:

探查器日志是：

SQL Statement: SET @col = NULL; SET @sql = NULL; Select Group_Concat(Distinct Concat( 'SUM(CASE WHEN tbl.sdate = ''', colname, ''' THEN tbl.result ELSE NULL END) AS ''', colname,'''' ) ) Into @col From ( select concat(month(i.invdate),'.',year(i.invdate)) as colname from invoices as i where i.invtype = 1 and i.pid = 5 order by i.invdate   ) As collst; SET @sql = CONCAT('SELECT tbl.wrkname,', @col, ' FROM ( Select wl.wgname As wrkname, Concat(Month(i.invdate),''.'',Year(i.invdate)) as sdate, Sum(id.qty * id.price) As result From invoices As i Join invoicedetails As id On i.pchid = id.pchid Join workgroups As w On i.wid = w.wid Join workgrouplist As wl On w.wglid = wl.wglid Where i.invtype = ', 1, ' And i.pid =', 5, ' Group By i.pid, sdate Order By i.invdate, wrkname ) AS tbl GROUP BY tbl.wrkname'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 
Start Time: 1388927869.2788
Final Time: 1388927869.2808
Total Elapsed Time: 0.0019619464874268

Problem solved: I create stored procedure and insert all query. Next i call stored procedure in php script.

问题已解决：我创建了存储过程并插入了所有查询。接下来我在 php 脚本中调用存储过程。

$sql = "CALL GetReportByProjectBetweenDates(1, 5)";
$result = $this->db->query($sql); 

回答by David Duncan

All I see is your SQL, do you want to show how you are trying to run it in phalcon?

我所看到的只是你的 SQL，你想展示你是如何尝试在 phalcon 中运行它的吗？

One of the easy ways that you can run raw SQL without using models or metadata is to use a base adapter, in this example I will use PDO:

在不使用模型或元数据的情况下运行原始 SQL 的一种简单方法是使用基本适配器，在本例中，我将使用 PDO：

        $connection = new Phalcon\Db\Adapter\Pdo\Mysql(array(
              'host' => 'localhost',
              'username' => 'user',
              'password' => 'password',
              'dbname' => 'optional'
        ));

        $connection->connect();

        $sql = 'select * from database.table';

        $result_set = $connection->query($sql);
        $result_set->setFetchMode(Phalcon\Db::FETCH_ASSOC);
        $result_set = $result_set->fetchAll($result_set);

回答by Marcelito Costa

To run a raw sql on a controller:

要在控制器上运行原始 sql：

$this->db->query("SELECT * FROM robots WHERE id > 0");

To run a raw sql on a model:

要在模型上运行原始 sql：

$this->getDi()->getShared('db')->query("SELECT * FROM robots WHERE id > 0");

回答by mosid

From the documentation:

从文档：

If Raw SQL queries are common in your application a generic method could be added to your model:

如果原始 SQL 查询在您的应用程序中很常见，则可以将通用方法添加到您的模型中：

<?php

use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class Robots extends Phalcon\Mvc\Model
{
    public static function findByRawSql($conditions, $params=null)
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE $conditions";

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(null, $robot, $robot->getReadConnection()->query($sql, $params));
    }
}  

回答by atzeatze

basend on the answer of david.duncan, working edition :P (missing commas added + array print)

基于david.duncan的答案，工作版：P（缺少逗号添加+数组打印）

$con = new Phalcon\Db\Adapter\Pdo\Mysql(array(
        'host' => 'localhost',
        'username' => '',
        'password' => '',
        'dbname' => 'aDatabase'
    ));

    $con->connect();
    $sql = 'select * from aDatabase.aTable';
    $result = $con->query($sql);
    $result->setFetchMode(Phalcon\Db::FETCH_ASSOC);
    $result = $result->fetchAll($result);

    foreach($result as $one)
    {
        echo print_r($one) . '<br>';
    }

回答by Saurabh Chandra Patel

$sql = 'SELECT * from table'; 
$query = $this->modelsManager->createQuery( $sql);
$logs  = $query->execute(); 

for modelsManager you need to setup in your service (service.php)

对于modelsManager，您需要在您的服务中进行设置（service.php）

$di->set('modelsManager', function() {
      return new Phalcon\Mvc\Model\Manager\ModelsManager();
});