Phalcon query with multiple models

Phanlcon, the php framework, provides a convenient Model->find() for data search, and can even give conditions, but in actual use, multiple models (tables) are often used for join, although pure sql can be used to add sql connection to complete, but the code will be more verbose, without considering the efficiency, you can use the query builder to complete.

The searchAction() created by scaffolding by phalcon dev-tool looks like this. It can easily receive data from the search form and convert it into search criteria. After completing the search, it will be sent to the front-end volt template and the screen will be generated. result.

 public function searchAction()
{
  $numberPage = 1;
  if ($this->request->isPost()) {
    $query = Criteria::fromInput($this->di, 'Modela', $_POST);
    $this->persistent->parameters = $query->getParams();
  } else {
    $numberPage = $this->request->getQuery("page", "int");
  }

  $parameters = $this->persistent->parameters;
  if (!is_array($parameters)) {
    $parameters = [];
  }
  $parameters["order"] = "sortfielda";

  $modela_list = Modela::find($parameters);
  if (count($modela_list ) == 0) {
    $this->flash->notice("The search did not find any data");

    $this->dispatcher->forward([
      "controller" => "controllera",
      "action" => "index"
    ]);

    return;
  }

  $paginator = new Paginator([
    'data' => $modela_list ,
    'limit'=> 10,
    'page' => $numberPage
  ]);

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

But in fact, it may be necessary to join Modelb with this Modela, and it is more convenient to read and query the data when the results are displayed on the screen at one time. Query builder can be used without considering the direct use of sql statement.

 public function searchAction()
{
  $numberPage = 1;
  if ($this->request->isPost()) {
    $query = Criteria::fromInput($this->di, 'Modela', $_POST);
    $this->persistent->parameters = $query->getParams();
  } else {
    $numberPage = $this->request->getQuery("page", "int");
  }

  $parameters = $this->persistent->parameters;
  if (!is_array($parameters)) {
    $parameters = [];
  }
  $parameters["order"] = "Modela.sortfielda"; //The sorting was originally sortfielda, in order to avoid field name repetition (ambiguous), change it to Modela.sortfielda

  //Add the field data for checking and querying Modelb if($_POST['fieldb'] != null)
  {
    $parameters["conditions"] = $parameters["conditions"] . " AND fielsb LIKE '%" . $_POST["fieldb"] . "%' ";
    $this->persistent->parameters = $parameters; // Remember to assign $parameters back to the persistent variable for use when changing pages}

  $q = $this->modelsManager->createBuilder($parameters)->addFrom('Modela'); //Originally Modela.find() now uses query builder, so manually specify from Modela
  $q->join("Modelb", "Modelb.joinfieldb = Modela.joinfielda"); // specify join
  $q->columns("Modela.fielda1, Modela.fielda2, Modela.fielda3, Modelb.fieldb1"); //Specify the fields to be selected $modela_list = $q->getQuery()->execute();
  if (count($modela_list ) == 0) {
    $this->flash->notice("The search did not find any data");

    $this->dispatcher->forward([
      "controller" => "controllera",
      "action" => "index"
    ]);

    return;
  }

  $paginator = new Paginator([
    'data' => $modela_list ,
    'limit'=> 10,
    'page' => $numberPage
  ]);

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

In this way, it is not necessary to change the volt template.

 {% if page.items is defined %}
{% for itm in page.items %}
  <tr>
    <td>{{ itm.fielda1}}</td>
    <td>{{ itm.fieldb1 }}</td>
    <td>{{ itm.fielda2 }}</td>
    <td>{{ itm.fielda3 }}</td>
    <td>{{ itm.fielda4 }}</td>
    <td>{{ itm.fielda5 }}</td>
    <td>{{ itm.fielda6 }}</td>
  </tr>
{% end for %}
{% endif %}

Original link: Phanix's Blog