Zend Framework 2 : Getting real Sql String of Zend\Db

I know, this is maybe a simple post for you, but it useful for me🙂. Hope it useful for someone. When we build a query using Zend\Db, sometime, complex query need checking for debugging to make sure our query is right. For example, we need to make sure we build a query like this :

SELECT `album`.* FROM `album` WHERE `title` = 'abracadabra' AND (`id` = '1' OR `artist` = 'Tony') LIMIT 1

And we already have an \Zend\Db\Sql\Select instance. In case if you have Model class like zf2 docs provide

$sql = $this->tableGateway->getSql();
$select = $sql->select();
$select->where(array('title' => 'abracadabra'));
$select->where
  ->NEST->
        equalTo('id', 1)
            ->OR->
        equalTo('artist', 'Tony')
  ->UNNEST;

$select->limit(1);

So, Before we return it with :

return $this->tableGateway->selectWith($select);

We can check it with :

echo $sql->getSqlstringForSqlObject($select); die ; // ( die/exit to debugging purpose )
//it will print sql string :
// SELECT `album`.* FROM `album` WHERE `title` = 'abracadabra' AND (`id` = '1' OR `artist` = 'Tony') LIMIT 1

That will return the real sql build for our specific platform.

references :
https://github.com/zendframework/zf2/issues/3224