具有多个模型的 Phalcon 查询
IPFS
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
喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!