How to run raw SQL Query with Zend Framework 2

问题:

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

I have a string like that:

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

now I want to execute this string directly.

回答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

回答2:

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

$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.

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

回答3:

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

$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.

回答4:

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

   $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.