MySQL 如何使用 Zend Framework 2 运行原始 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15332671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to run raw SQL Query with Zend Framework 2
提问by nopaws
Is there a way to execute a SQL String as a query in Zend Framework 2?
有没有办法在 Zend Framework 2 中将 SQL 字符串作为查询执行？
I have a string like that:
我有一个这样的字符串：
$sql = "SELECT * FROM testTable WHERE myColumn = 5"
now I want to execute this string directly.
现在我想直接执行这个字符串。
采纳答案by automatix
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
回答by Weteef
Just pass the sql string to your db adapter like this:
只需将 sql 字符串传递给您的数据库适配器，如下所示：
$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\ResultSetotherwise(INSERT, UPDATE, DELETE, ...) it will return a \Zend\Db\Adapter\Driver\ResultInterface.
编辑：请注意，查询方法并不总是返回结果集。当它是一个产生 query( SELECT)的结果集时，它返回一个\Zend\Db\ResultSet\ResultSetelse( INSERT, UPDATE, DELETE, ...) 它将返回一个\Zend\Db\Adapter\Driver\ResultInterface。
And when you leave the second Parameter empty you will get a \Zend\Db\Adapter\Driver\StatementInterfacewhich you can execute.
当您将第二个参数留空时，您将获得一个\Zend\Db\Adapter\Driver\StatementInterface可以执行的参数。
回答by automatix
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 语句）的查询很有用。
回答by FlameStorm
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 中仍然不是很聪明。很高兴看到它是否对某人有帮助。
