Zend-framework Query TOP

Question

Hello i try generate a query in Zend 2 just like this

 select top 10 * from mensaje where idUsuario = 11 order by fechaAltaMensaje DESC

i try use this

$select = $sql->select();
   $select->from('mensaje');
   $select->where('idUsuario = '.$idUser.' order by fechaAltaMensaje DESC');
$select->limit(5);

but don't work

Answer 1

You are missing some details in your code in order for it to work,

please see below.

$adapter = $this->tableGateway->getAdapter();//use Zend\Db\TableGateway\TableGateway;
$sql = new Sql($adapter);//use Zend\Db\Sql\Sql;
$select = $sql->select();
$select->from('mensaje');
$select->where('idUsuario = '.$idUser.'');
$select->order('fechaAltaMensaje DESC');
$select->limit(5);
$selectString = $sql->getSqlStringForSqlObject($select);//print_r($selectString);die; //gives you the query in string
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
$resultSet = new ResultSet();//use Zend\Db\ResultSet\ResultSet;
$resultSet->initialize($results);
return $resultSet->toArray();//the result to array

Please read the tutorials below and you will get the full picture

Examples

Examples 2

Answer 2

The limit function only applies to platforms that support it. To achieve what you're after in SQL you need to use the quantifier function.

Also - where accepts an array of column => value pairs.

And there is an order function that accepts a column name and direction:

$select = $sql->select();
$select->from('mensaje')
       ->where(['idUsuario' => $idUser])
       ->order('fechaAltaMensaje DESC')
       ->quantifier('TOP(5)')

I am not pleased with Zends implementation of the sql abstraction layer, when you need to use two different functions to write SQL that is not cross platform to do simple things like limit or top. That's just my two pence.