具有多个模型的 Phalcon 查询

Phanlcon 这个php framework 提供方便的Model->find() 来做资料搜寻，甚至还可以给予条件，但在实际使用的时候，常常会是多个model (table) 做join，虽然可以用纯sql 加上sql connection 来完成，但是程式码就是会冗长一些，在不考虑效率的状况下，可以改用query builder 来完成。

由phalcon dev-tool 做scaffold 出来的searchAction() 大概长这个样子，可以很方便的接收来自搜寻表单的资料，并转成search criteria，做完搜寻之后丢会去给前端的volt template 然后产生出画面结果。

 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();
}

但实际上可能需要将Modelb 来跟这个Modela 做join，所得到的结果一次呈现在画面上会比较方便阅读与查询资料。在不考虑直接用sql statement 的状况下，可以用query builder。

 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"; //排序原本是sortfielda, 为了避免field name 重复(ambiguous)，改成Modela.sortfielda

  //增加检查查询Modelb 的栏位资料if($_POST['fieldb'] != null)
  {
    $parameters["conditions"] = $parameters["conditions"] . " AND fielsb LIKE '%" . $_POST["fieldb"] . "%' ";
    $this->persistent->parameters = $parameters; // 记得将$parameters 指定回persistent 变数内，供换页的时候使用}

  $q = $this->modelsManager->createBuilder($parameters)->addFrom('Modela'); //原本是Modela.find() 现在改用query builder，所以要手动指定from Modela
  $q->join("Modelb", "Modelb.joinfieldb = Modela.joinfielda"); // 指定join
  $q->columns("Modela.fielda1, Modela.fielda2, Modela.fielda3, Modelb.fieldb1"); //指定要选取的栏位$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();
}

用这样的方式在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>
{% endfor %}
{% endif %}

Original link: Phanix's Blog