[SOLVED] Using combine and order together with Table Gateway in ZF2

Using combine and order together with Table Gateway in ZF2

When adding $select->order(...) to a previously combined Select in Zend Framework 2 the "order by" will only be added to the first Select statement.

SELECT `user`.* 
FROM `user` WHERE `user_id` = :where1 
ORDER BY `starttime_dt` ASC ) 
UNION 
( SELECT `user`.* 
FROM `user` 
WHERE `user_id` != :subselect1where1 )

What I need is that the sorting will be applied to the full Select. A solution is already described here

https://github.com/zendframework/zf2/issues/5162#issuecomment-36294281

But this solution works with an SQL-Adapter.

How can I do that with a Table Gateway?

My code looks currently like this (resulting to the above statement)

$select = $this->tableGateway->getSql()->select();
$select2 = clone $select;
$select->where->equalTo('user_id', $user_id);
$select2->where->notEqualTo('user_id', $user_id);
$select->combine($select2);
$select->order('starttime_dt');
$resultSet = $this->tableGateway->selectWith($select);

Solution

You would do it with table gateway the same way it was illustrated in the link you provided.

Your code would look like

$select = $this->tableGateway->getSql()->select();
$select2 = clone $select;
$select->where->equalTo('user_id', $user_id);
$select2->where->notEqualTo('user_id', $user_id);
$select->combine($select2);

$combinedSelect = (new Zend\Db\Sql\Select)->from(['sub' => $select])->order('starttime_dt');

$statement = $this->sql->prepareStatementForSqlObject($combinedSelect);
$result = $statement->execute();
$resultSet = clone $this->resultSetPrototype;
$resultSet->initialize($result);

You wouldn't be able to use the TableGateway's selectWith method because it's going to check that the provided Select's table matches the TableGateway's table.