We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Paginator, QueryBuilder, orderBy and Postgresql

hi, in 1.1.0 It seems that the query, which counts total rows, still contains an ORDER BY statement.. Maybe it is ok with mysql, but Postgresql throws an error

SQLSTATE[42803]: Grouping error: 7 ERROR: column "test" must appear in the GROUP BY clause or be used in an aggregate function LINE 1


98.9k

An order by? or group by?

an order by,

<?php
        $builder = $this->getModelsManager()->createBuilder()
            ->from('table')
            ->orderBy('id DESC')
            ;
        $pager = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
            'builder' => $builder,
            'limit' => 10,
            'page' => 1,
        ));
        $pager->getPaginate();

will result in something like this

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42803]: Grouping error: 7 ERROR: column "id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...(*) AS "rowcount" FROM "table" ORDER BY "id... ^' in 

an ORDER BY fields should be omitted, when generating count(*) query.. otherwise postgres requires those fields to be grouped



98.9k

I removed the ORDER BY from the total query in 1.2.0, can you try please?



1.7k
Accepted
answer

yes, it works as expected.. thank you! :)

edited Oct '14

In case of a postgres database there is a similar problem for clause group by

SELECT<sql> FROM <sql query here using GROUP BY> DESC LIMIT 1
SELECT COUNT(*) AS "rowcount" FROM <sql query here using GROUP BY>

the clause GROUP BY it is not removed from total query as it happend with ORDER BY.

Database is postgres, phalcon 1.2.1