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

Model PHQL Pagination and Namespace

1) How I can get pagination for PHQL query correctly? 2) How I can alias namespace for model in query?

I use createBuilder method:

$tickets = $this->modelsManager->createBuilder()
                    ->from('Tickets')
                    ->join('AreaSectors')
                    ->join('Orders')
                    ->where('Tickets.eventId = :eventId:', array('eventId' => $eventId))
                    ->getQuery()
                    ->execute();

I get error quoteModel 'Tickets' could not be loaded I use ->registerNamespaceAlias() before query, but this not help me

$this->modelsManager->registerNamespaceAlias('Tickets', 'Application\Models\Tickets');
$this->modelsManager->registerNamespaceAlias('AreaSectors', 'Application\Models\AreaSectors');
$this->modelsManager->registerNamespaceAlias('Orders', 'Application\Models\Orders');

Why? And how I can set pagination for this query and get rows at the same time? If I use creatBuilder I not get property in view from joined tables :(

edited Oct '14

2 is a big mystery to me too.

For pagination, i've been adapting the code from sfDoctrinePager (Symfony model paginator) - https://trac.symfony-project.org/wiki/sfDoctrinePager .

It accepts an extra query to generate the counts, set the count (if you fetch the numbers from SOLR for example) and set adjust different numbers on the first and next pages (eg: page1=10 results, page2=20 results).

Please note that the following code is just a draft and i will clear it pretty soon:

<?php
/**
 * Project Zero
 * User: Marian Craciun
 * Date: 1/13/14 11:32 AM
 */

namespace Zero\Pager;

use Phalcon\Exception;
use Zero\Context\Zero;

class ModelPager  {

  /**
   * @var array
   */
  protected $options = array(
    'max_per_page' => 10,
    'max_per_page_one' => null,
  );

  /**
   * @param mixed $count
   */
  public function setCount($count)
  {
    $this->count = $count;
  }

  /**
   * @return mixed
   */
  public function getCount()
  {
    $this->init();
    return $this->count;
  }

  /**
   * @param mixed $page
   */
  public function setPage($page)
  {
    $this->page = $page;
  }

  /**
   * @return mixed
   */
  public function getPage()
  {
     return $this->page;
  }

  /**
   * @param \Phalcon\Mvc\Model\Criteria|\Phalcon\Mvc\Model\Query\Builder $query
   */
  public function setQuery($query)
  {
    $this->query = $query;
  }

  /**
   * @return \Phalcon\Mvc\Model\Criteria|\Phalcon\Mvc\Model\Query\Builder
   */
  public function getQuery()
  {
    return $this->query;
  }

  /**
   * @param \Phalcon\Mvc\Model\Criteria|\Phalcon\Mvc\Model\Query\Builder
   */
  public function setQueryCount($queryCount)
  {
    $this->queryCount = $queryCount;
  }

  /**
   * @return \Phalcon\Mvc\Model\Criteria|\Phalcon\Mvc\Model\Query\Builder
   */
  public function getQueryCount()
  {
    return $this->queryCount;
  }

  /**
   * @var \Phalcon\Mvc\Model\Criteria
   */
  protected $query;

  /**
   * @var \Phalcon\Mvc\Model\Criteria
   */
  protected $queryCount;

  /**
   * @var \Phalcon\Mvc\Model\Resultset\Simple
   */
  protected $results;

  protected $count;

  /**
   * @param mixed $lastPage
   */
  public function setLastPage($lastPage)
  {
    $this->lastPage = $lastPage;
  }

  /**
   * @return mixed
   */
  public function getLastPage()
  {
    $this->init();
    return $this->lastPage;
  }
  protected $page;
  protected $lastPage;

  public function setOption($option, $value)
  {
    $this->options[$option] = $value;
    return $this;
  }
  public function getOption($option)
  {
    return $this->options[$option];
  }

  public function setMaxPerPage($value)
  {
    return $this->setOption('max_per_page', $value);
  }
  public function setMaxPerPageOne($value)
  {
    return $this->setOption('max_per_page_one', $value);
  }

  public function haveToPaginate()
  {
    if($this->getOption('max_per_page_one') !== null && $this->getOption('max_per_page_one') != $this->getOption('max_per_page')) {
      return $this->getOption('max_per_page') && $this->getLastPage() > 1;
    }
    else {
      return $this->getOption('max_per_page') && $this->count > $this->getOption('max_per_page');
    }
  }

  /**
   * Returns an array of page numbers to use in pagination links.
   *
   * @param  integer $nb_links The maximum number of page numbers to return
   *
   * @return array
   */
  public function getLinks($nb_links = 5)
  {
    $links = array();
    $tmp   = $this->page - floor($nb_links / 2);
    $check = $this->lastPage - $nb_links + 1;
    $limit = $check > 0 ? $check : 1;
    $begin = $tmp > 0 ? ($tmp > $limit ? $limit : $tmp) : 1;

    $i = (int) $begin;
    while ($i < $begin + $nb_links && $i <= $this->lastPage)
    {
      $links[] = $i++;
    }

    return $links;
  }

  protected $_urlParams;
  public function setCurrentUri($url= NULL){
    if(!$url) {
      $url= Zero::instance()->request()->getServer('REQUEST_URI');
    }
    $urlParams= parse_url($url);
    if(isset($urlParams['query'])) {
      parse_str($urlParams['query'], $urlParams['query']);
    }
    else {
      $urlParams['query']= array();
    }
    $this->_urlParams= $urlParams;
  }

  public function LinkToPage($page_id){
    if(!$this->_urlParams) {
      $this->setCurrentUri();
    }

    if($page_id > 1) $this->_urlParams['query']['page']= $page_id;
    elseif(isset($this->_urlParams['query']['page'])) unset($this->_urlParams['query']['page']);
    $query = http_build_query($this->_urlParams['query']);
    if(strlen($query) > 0)
      return sprintf('%s?%s',
        $this->_urlParams['path'],
        http_build_query($this->_urlParams['query']));
    else {
      return $this->_urlParams['path'];
    }
  }

  /**
   * @param $query
   * @return ModelPager
   */
  public static function fromQuery($query){
    $pager = new self();
    $pager->page = Zero::instance()->request()->get('page', null, 1);
    $pager->query = $query;
    return $pager;
  }

  protected $_init;
  public function init($force = false)
  {
    if($force == false && $this->_init == true) return $this;
    $this->_init = true;

    if($this->count === null) {
      $count= $this->computeCount();
      $this->count= $count;
    }

    $offset=0;
    $max_per_page= $this->getOption('max_per_page');
    $max_per_page_one = $this->getOption('max_per_page_one');
    $limit= $max_per_page;

    //Few rules will reset everything to page=1
    if (0 == $this->page || 0 == $max_per_page || 0 == $this->count)
    {
      $this->setLastPage(0);
    }
    else
    {

      if($max_per_page_one !== null && $max_per_page != $max_per_page_one) {
        $this->lastPage = (ceil(max(($this->count - $max_per_page_one),0) / $max_per_page) + 1);
        if($this->page < 2){
          $offset = 0;
          $limit = $max_per_page_one;
        }
        else {
          $offset = (($this->page - 2) * $max_per_page) + $max_per_page_one;
        }
      }
      else {
        $offset = ($this->page - 1) * $max_per_page;
        $this->lastPage =  ceil($this->count / $max_per_page);
      }
    }

    $this->query->limit($limit, $offset);
  }

  /**
   * Retrieve the object for a certain offset
   *
   * @param integer $offset
   *
   * @return Doctrine_Record
   */
  protected function retrieveObject($offset)
  {
    $queryForRetrieve = clone $this->getQuery();
    $queryForRetrieve
      ->offset($offset - 1)
      ->limit(1)
    ;

    $results = $queryForRetrieve->execute();

    return $results[0];
  }

  public function computeCount()
  {
    if($this->getQueryCount()) {
      $query= $this->getQueryCount();
    }
    else {

      $query = clone($this->query);
      $class = get_class($query);
      if($class == 'Phalcon\Mvc\Model\Criteria') {
        $query->columns(array('COUNT(*) as num_results'))->limit(1);
      }
      else if($class == 'Phalcon\Mvc\Model\Query') {
        throw new Exception('Phalcon\Mvc\Model\Query does not supports generating count queries. One needs to be set.');
      }
      else if($class == 'Phalcon\Mvc\Model\Query\Builder') {
        $query->columns(array('COUNT(*) as num_results'))->limit(1);
      }
      else {
        throw new Exception($class.' is not implemented for count queries.');
      }
     }

    $class = get_class($query);

    if($class == 'Phalcon\Mvc\Model\Criteria') {
      return $query->execute()->getFirst()->num_results;
    }
    else if($class == 'Phalcon\Mvc\Model\Query') {
      throw new Exception('Phalcon\Mvc\Model\Query does not supports generating count queries. One needs to be set.');
    }
    else if($class == 'Phalcon\Mvc\Model\Query\Builder') {
      return $query->getQuery()->execute()->getFirst()->num_results;
    }
    else {
      throw new Exception($class.' is not implemented for count queries.');
    }

  }

  /**
   * @return \Phalcon\Mvc\Model\ResultsetInterface
   */
  public function getResults()
  {
    $this->init();
    $class = get_class($this->query );
    if($class == 'Phalcon\Mvc\Model\Criteria') {
      return $this->query->execute();
    }
    else if($class == 'Phalcon\Mvc\Model\Query') {
      throw new Exception('Phalcon\Mvc\Model\Query does not supports generating count queries. One needs to be set.');
    }
    else if($class == 'Phalcon\Mvc\Model\Query\Builder') {
      return $this->query->getQuery()->execute();
    }
    else {
      throw new Exception($class.' is not implemented for query results.');
     }

  }
} 

It is supposed to be used like this:


    $q= \Zero\Models\User::query($this->getDI())
      ->columns(array('\Zero\Models\User.*', 'up.*'))
      ->setModelName('\Zero\Models\User')
      ->innerJoin('\Zero\Models\UserProfile', '\Zero\Models\User.user_id = up.user_id' ,'up');

$q = \Zero\Context\Zero::instance()->modelsManager()->createBuilder()
      ->from('User')
      ->leftJoin('\Zero\Models\UserProfile','u.user_id = up.user_id','up')
      ->limit(2);

    $pager = \Zero\Pager\ModelPager::fromQuery($q)->setMaxPerPage(2);