Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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 ```sql SQLSTATE[42803]: Grouping error: 7 ERROR: column "test" must appear in the GROUP BY clause or be used in an aggregate function LINE 1 ```



83.0k

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 ```sql 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



83.0k

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



1.1k
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