SELECT (COUNT(*)) query in ZF2

Question

I'm trying to execute SQL query with table gateway which contain COUNT(*) expression in ZF2. This is function in my model:

public function brKomentariUred(){
        $sql = $this->tableGateway->getSql();
        $select = $sql->select();
        $select->columns(array('brKomentari' => new \Zend\Db\Sql\Expression('count(komentarID)'), 'uredId' => 'ured'));
        $select->group('ured');
        //echo $sql->getSqlStringForSqlObject($select); die();
        return $this->tableGateway->selectWith($select);      
    }

When the query is printed it is correct

SELECT count(komentarID) AS `brKomentari`, `komentar`.`ured` AS `uredId` FROM `komentar` GROUP BY `ured`

In the controller I'm trying to call the query with this code

foreach($this->getKomentarTable()->brKomentariUred() as $r){
                $arr = $this->object_to_array($r);
                print_r($arr);
            }

It doesn't return number of elements and devicesID as it is written in SELECT, but return as SELECT * FROM komentar, but with no values. Is this right code or I'm making some error in my code? Other queries are OK.

Thanks in advance for your help.

"devicesID as it is written in SELECT" - there's no devicesID column in your query — RomanPerekhrest
I mean 'ured'. 'ured' is column in my table. The result which I get is this Array ( [komentarID] => [datum] => [sodrzina] => [ured] => [korisnik] => [inputFilter] => ) Array ( [komentarID] => [datum] => [sodrzina] => [ured] => [korisnik] => [inputFilter] => ) All of the elements in the arrays, except InputFilter, are columns in my table in the database — Vasil

RomanPerekhrest RomanPerekhrest · Accepted Answer · 2016-04-06T20:29:11

In your place I would do the following steps:

replace the expression object with new \Zend\Db\Sql\Expression('COUNT(komentarID)')
I wouldn't use an alias in group by operator, it may not work. So, replace this $select->group('ured') with $select->group('komentar.ured')

Also, the result processing should be simplified:

$resultSet = $this->getKomentarTable()->brKomentariUred();
print_r($resultSet->toArray());

SELECT (COUNT(*)) query in ZF2

1 Answers