Zend\Db\Sql and Aggregate Functions

  • Viewed 242×

  • Blog

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;
    }
}