Phalcon query with multiple models
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