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

Distinct in SQL with a paginator

I am trying to create a page that displays data from a table in a Paginator but that does a select distinct:

public function indexAction()
    {

      $numberPage = 1;
      //$query = Criteria::fromInput($this->di,'dsmc\Models\StudiesAccess',$this->request->getPost());

      $identity=$this->auth->getIdentity();
      $query = $this->modelsManager->createQuery("select distinct study_number, study_title from StudiesAccess where StudiesAccess.uni = '".$identity['uni']."'");

      echo($query->getBindParams());

      $this->persistent->searchParams = $query->getBindParams();
      $numberPage = $this->request->getQuery('page','int');

      $parameters = array();
      if($this->persistent->searchParams){
    $parameters = $this->persistent->searchParams;
      }

      $studies = StudiesAccess::find($parameters);
      if(count($studies) == 0){
    $this->flash->notice("The search did not find any patient intake forms");
    return $this->dispatcher->forward(array("action"=>"index"));
      }

      $paginator = new Paginator(array("data"=>$studies,"limit"=>10,"page"=>$numberPage));

      $this->view->page = $paginator->getPaginate();
    }

And the distinct directive is being ignored and so is the where clause. In the paginator when the page renders it displays all StudiesAccess records. What am I doing wrong? Is there any way to do this using a paginator?



40.7k
Accepted
answer

Never mind - I realized that I needed to put the criteria in the ::find call a little farther down. It works now!