如何使用Zend Framework 2运行原始SQL查询

Is there a way to execute a SQL String as a query in Zend Framework 2?

有没有办法在Zend Framework 2中执行SQL String作为查询？

I have a string like that:

我有一个这样的字符串：

$sql = "SELECT * FROM testTable WHERE myColumn = 5"

now I want to execute this string directly.

现在我想直接执行这个字符串。

4 个解决方案

#1

use Zend\Db\Sql\Sql;
use Zend\Db\Adapter\Adapter;

$dbAdapterConfig = array(
    'driver'   => 'Mysqli',
    'database' => 'dbname',
    'username' => 'dbusername',
    'password' => 'dbuserpassword'
);
$dbAdapter = new Adapter($dbAdapterConfig);

$sql = new Sql($dbAdapter);
$select = $sql->select();
$select->from('testTable');
$select->where(array('myColumn' => 5));

$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

S. docu: Zend\Db → Zend\Db\Sql

S. docu：Zend \ Db→Zend \ Db \ Sql

#2

Just pass the sql string to your db adapter like this:

只需将sql字符串传递给db适配器，如下所示：

$resultSet = $adapter->query($sql, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

And if you want to pass parameters:

如果你想传递参数：

$sql = "SELECT * FROM testTable WHERE myColumn = ?";
$resultSet = $adapter->query($sql, array(5));

EDIT: Please note that the query method does not always returns a resultset. When its a resultset producing query(SELECT) it returns a \Zend\Db\ResultSet\ResultSet otherwise(INSERT, UPDATE, DELETE, ...) it will return a \Zend\Db\Adapter\Driver\ResultInterface.

编辑：请注意，查询方法并不总是返回结果集。当它的结果集产生查询（SELECT）时，它返回\ Zend \ Db \ ResultSet \ ResultSet否则（INSERT，UPDATE，DELETE，...）它将返回\ Zend \ Db \ Adapter \ Driver \ ResultInterface。

And when you leave the second Parameter empty you will get a \Zend\Db\Adapter\Driver\StatementInterface which you can execute.

当您将第二个参数留空时，您将获得一个可以执行的\ Zend \ Db \ Adapter \ Driver \ StatementInterface。

#3

If you are using tableGateway, you can run your raw SQL query using this statement,

如果您正在使用tableGateway，则可以使用此语句运行原始SQL查询，

$this->tableGateway->getAdapter()->driver->getConnection()->execute($sql);

where $sql pertains to your raw query. This can be useful for queries that do not have native ZF2 counterpart like TRUNCATE / INSERT SELECT statements.

其中$ sql与您的原始查询有关。这对于没有本机ZF2对应项（如TRUNCATE / INSERT SELECT语句）的查询非常有用。

#4

If you have EntityManager $em on your hands, you can do something like this:

如果你手上有EntityManager $ em，你可以这样做：

   $select = $em->getConnection()->executeQuery("
        SELECT a.id, a.title, a.announcement, asvc.service_id, COUNT(*) AS cnt,
            GROUP_CONCAT(asvc.service_id SEPARATOR \", \") AS svc_ids
        FROM article AS a
        JOIN articles_services AS asvc ON asvc.article_id = a.id
        WHERE
        asvc.service_id IN (
            SELECT tsvc.service_id
            FROM tender AS t
            JOIN tenders_services AS tsvc ON tsvc.tender_id = t.id
            WHERE t.id = :tenderId
        )
        GROUP BY a.id
        ORDER BY cnt DESC, a.id DESC
        LIMIT :articlesCount
    ", [
        'articlesCount' => 5,
        'tenderId' => $tenderId,
    ], [
        'articlesCount' => \PDO::PARAM_INT,
    ]);

    $result = $select->fetchAll(); // <-- here are array of wanted rows

I think this way to execute complex queries is best for Zend. But may be I'm not very smart in Zend still. Will glad to see if it helps to someone.

我认为这种执行复杂查询的方式最适合Zend。但也许我在Zend中并不是很聪明。很高兴看到它对某人有帮助。

#1

use Zend\Db\Sql\Sql;
use Zend\Db\Adapter\Adapter;

$dbAdapterConfig = array(
    'driver'   => 'Mysqli',
    'database' => 'dbname',
    'username' => 'dbusername',
    'password' => 'dbuserpassword'
);
$dbAdapter = new Adapter($dbAdapterConfig);

$sql = new Sql($dbAdapter);
$select = $sql->select();
$select->from('testTable');
$select->where(array('myColumn' => 5));

$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();