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

DATE_FORMAT group bug with createBuilder and Paginator

        $model = new StatReport();
        $builder = $model->getModelsManager()->createBuilder()->from(get_class($model));
//        $results = $builder->columns('report_at')
        $results = $builder->columns('DATE_FORMAT(report_at,"%x%v") AS test')
            ->where('report_at> "2018-09-08"')
            ->groupBy('test')
//            ->groupBy('report_at')
            ->getQuery()->execute();
        $paginator = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
            "builder" => $builder,
            "limit"=> 10,
            "page" => 1,
        ));
        var_dump($paginator->getPaginate()->items->toArray());

throw:

Column 'test' doesn't belong to any of the selected models (1), when preparing: SELECT COUNT(DISTINCT test) AS [rowcount] FROM [StatReport] WHERE report_at> "2018-09-08"

if not use paginate or not use DATA_FORMAT, it work

how can i fix this problem?

my phalcon version is 3.3.1

thx



3.9k

A pagninator needs to count how many rows you're looking for. To do this, he changes the select clause to do a count. This will returns how many row match your where clause. You can't use terms like (group by) or filtering (having) in the query if you want to paginate, because in count, this doesn't exist anymore.

SELECT COUNT(DISTINCT test) AS [rowcount] FROM [StatReport] WHERE report_at> "2018-09-08"