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.
Add a comment
|
4 Answers
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.
1 Comment
Kamlesh
@Clarissa Which link? Please share.
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
5 Comments
Clarissa
I am using this approach to execute some complex queries directly in a controller and it works fine, but the problem is that I need to declare the adapter in every action where I need it, which is redundant. I would like to create the adapter only once and then call it in the actions where I need it, but I haven´t figured out how to do it... so my question is, How and where should you declare or create the dbAdapter in a way that later on you can just call it in different actions in a controller, without having to declare it everytime you need to execute a different query?
automatix
@Clarissa You should never execute SQL in a controller. It breaks the MVC priciple. The Communication with the database is the job of the model.
Clarissa
@Automatix I know... but I am recently starting to use Zend framework and I have had a hard time to making it work properly with this case. In most of my application I am using the correct MVC principle, where all the data access happens in the model. I know the controllers are supposed to be thin, but in this particular case, I need to execute 2 complex queries that include 5 tables and one stored procedure each. I didn't know how to process according to the standard, and this was the way that seemed to work, even when it is not the proper one... that´s why i am still trying to fix it.
zelibobla
This is not raw SQL query launch. You did not answer the question.
Dave Heq
@Clarissa (or anybody reading this) To keep data-fetching queries out of your controllers, simply have the controller call a function in the model that builds and executes the query, even if it means making multiple functions in the model that are called by the initial function.
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.
Comments
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.
