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. Beginners

How to get resultset and total

How to achieve this query in phalcon using query builder (best method):

$results = " select * from table where name like 'A%' limit 10 ";  // <-- 10 records

$total = " select count(1) from table where name like 'A%' "; // <-- 100 records

return [
    'data' => $result,
    'total' => $total
];

im extjs user and i need to get total over limit to display paging information (eq: displaying 1 to 10 from 100 records)

thx.

are these methods valid?

$builder = new Builder();
$results = $builder
   ->from(__CLASS__)
   ->where('name like :name:', array('name'=>'A%'))
   ->andWhere(...)
   ->andWhere(...)
   ->orderby(...)
   ->limit(10)
   ->getQuery()
   ->execute();

$cloned = clone($builder);
$total = $cloned
    ->limit(null)
    ->getQuery()
    ->execute()
    ->count();


51.2k
Accepted
answer

I don't use query builder. But if it helps, here is a more simple approach:

        $itemsPerPage = 10;
        $currentPage = 1; // Implement for example $this->request->getQuery('page', 'int', 1)

        $result = YourTableTableModel::find(array(
            "name LIKE %:name:%",
            "bind" => array("name" => $name)
        ));

        $paginator = new \Phalcon\Paginator\Adapter\NativeArray(
            array(  
                "data" => $result,
                "limit"=> $itemsPerPage,
                "page" => $currentPage
            )
        );

        return $paginator->getPaginate();


5.1k

helpful thanks