This is just an example of how to include aggregate function call when building SQL queries via Zend\Db\Sql.
A model class using Adapter and reading from Microsoft SQL Server:
<?php
namespace Traffic\Model;
use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Sql;
class Units
{
const TABLE_NAME = 'units';
public $adapter;
public function __construct(Adapter $adapter)
{
$this->adapter = $adapter;
}
/**
* @param array $filter
*/
public function selectExample(array $filter)
{
$dateTime = \DateTime::createFromFormat('d.m.Y', $filter['date_from']);
$sql = new Sql($this->adapter, self::TABLE_NAME);
$select = $sql->select();
$select->columns(array(
'id' => 'ID',
'date_part' => new \Zend\Db\Sql\Expression('CONVERT(VARCHAR(24), TimeStmp, 105)'),
'time_part' => new \Zend\Db\Sql\Expression('CONVERT(VARCHAR(24), TimeStmp, 108)'),
'name' => 'TagName',
));
$select->where->expression('CONVERT(date, CreatedAt) >= ?', $dateTime->format('Y-m-d'));
$select->order('TimeStmp DESC');
$select->quantifier('TOP 25'); // SELECT TOP 25 ...
// echo sql query string
echo $sql->buildSqlString($select);
// or from $select object
echo $select->getSqlString($this->adapter->getPlatform());
exit;
}
}