Hi,
To perform the sql function inside sql query in ZF2 like:
Type 1:
Query:
Type 2:
To perform the sql function inside sql query in ZF2 like:
Type 1:
Query:
"select count(*) from foo group by name" ZF2: $sql = new Sql($adaptor);
$select = $sql->select()->from(array('f' => 'foo'));
$select = $select->columns(array('count' => new \Zend\Db\Sql\Expression("count(*)")));
$select = $select->group(array('name'));
$statement = $sql->prepareStatementForSqlObject($select);
echo $statement->getSql(); Type 2:
Query = "Select * from foo where foo_name = lower('test')"; ZF2: $sql = new SQL($adaptor);
$select = $sql->select()->from(array('f'=>'foo'));
$select = $select->where('foo_name' => new \Zend\Db\Sql\Expression("LOWER('test')"));
$statement = $sql->prepareStatementForSqlObject($select);
echo $statement->getSql(); Type 3:
Manage sub-query inside query:
Query:
SELECT `comment`.`id` AS `commentId`, `comment`.`comment` AS `comment`,
(SELECT COUNT(`comment_vote`.`id`) AS `negativeVote`
FROM `comment_vote`
WHERE vote = -1
AND `comment_vote`.`commentId` = `comment`.`id`) AS `nagetiveVoteCount`
FROM `comment`
ZF2: $sub = new Select('comment_vote');
$sub->columns(array('negativeVote' => new \Zend\Db\Sql\Expression('COUNT(comment_vote.id)')), FALSE)->where(array('vote' => -1 , 'comment_vote.commentId' => 'comment.id'));
$subquery = new \Zend\Db\Sql\Expression("({$sub->getSqlString()})");
$predicate = new \Zend\Db\Sql\Predicate\Expression("({$sub->getSqlString()})");
$sql = new Sql($this->adapter);
$select = $sql->select()->from('comment');
$select->columns(array('commentId','comment', 'nagetiveVoteCount' => $subquery));
echo $select->getSqlString();
Union of Sql Query:
Union of Sql Query:Query:
select 'passport' as type,a.user_id from join_user_passport_office as a where a.user_id=7
union
select 'embassy' as type,b.user_id from join_user_embassy_office as b where b.user_id=7
union
select 'visa' as type,c.user_id from join_user_visa_office as c where c.user_id=7
union
select 'ecowas' as type,d.user_id from join_user_ecowas_office as d where d.user_id=7
union
select 'freezone' as type,e.user_id from join_user_freezone_office as e where e.user_id=7 ;
ZF2:
$select1 = $this->_sql->select()
->from(array('t1' => 'join_user_embassy_office'))
->columns(array('type' => new \Zend\Db\Sql\Expression("'embassy'"), 'user_id' => 'user_id'))
->where(array('t1.user_id' => $id));
$select2 = $this->_sql->select()
->from(array('t2' => 'join_user_passport_office'))
->columns(array('type' => new \Zend\Db\Sql\Expression("'passport'") ,'user_id' => 'user_id'))
->where(array('t2.user_id' => $id));
$select1->combine($select2);
$select3 = $this->_sql->select()
->from(array('t3' => 'join_user_visa_office'))
->columns(array('type' => new \Zend\Db\Sql\Expression("'visa'") , 'user_id' => 'user_id'))
->where(array('t3.user_id' => $id));
$selectall3 = $this->_sql->select();
$selectall3->from(array('sel1and2' => $select1));
$selectall3->combine($select3);
