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
Advertisements
How to output in Doctrine case?
$this->queryBuilder = $this->getEntityManager()->createQueryBuilder();
$this->queryBuilder->select(“c”)->from($this->getEntityName(), “c”);
Then i’m build some query and then get it
$query = $this->queryBuilder->getQuery();
Now i’m can output it like this
\Doctrine\Common\Util\Debug::dump($query->getSQL());
But there i’m will see ? in places, where i placed params.
How to see query with binded param values?
that’s not related with zf2 i think 🙂
I normally use $select->getSqlString(). Is there a difference?
answered on fb 😉
Hi Samsonasik,
Thanks for the useful post. Waiting for the post about Apigility…. Hope it would be coming soon
thanks. I hope I have time to do it 😉
sir, i have been searching calender or date code, alongwith upload fiels which shows progress meter am not getting it
1) in one form i should have fields which have date field but when u click inside text box calendar should open 2) upload form for all types doc/pdf/image/txt with multiple which also shows progress in percentage 3) when u click submit or save button all above function should save to database and upload to particular path 5) i have tried zf2fileupload examples but not getting progress bar 6) date i have used combo box
Thanks for these great tutorials! These have saved me many hours.
Thanks. :)))
Hi Adbul,
How do you get the sql for an insert? (Except from using a debugger)
Hi Bart,
As far as I know, the way to do for insert is by define insert object:
use Zend\Db\Sql\Insert; $insertObj = new Insert; $insertObj->into('test'); $insertObj->values(array('name' => 'samsonasik')); echo $this->tableGateway->getSql()->getSqlStringForSqlObject($insertObj);;
Nice one! I ended up using the debugger to step through and find the generated sql that way.