Cakephp2 独自SQLでPaginateしたやつのソートと検索。
これもはまった。
独自SQLでPaginateするのはググると結構出てくるので割愛。
ソートにはまった。はまったはまった。
結論:「virtualFields」を使う。
あ…割愛しないでやっぱり書きます。えぇ、将来の自分のためにですとも!!
PaginateOrigin.php(この子があることで独自SQLでのPaginateが可能になります)
App::uses('AppModel', 'Model'); class PaginateOrigin extends AppModel { public $name = 'PaginateOrigin'; public $useTable = false; public $virtualFields = array( 'teh_experience_id' => 'PaginateOrigin.experience_id', 'teh_nickname' => 'PaginateOrigin.nickname', 'teh_edit_time' => 'PaginateOrigin.edit_time', ); /** * ページネート実行 未審査取得 * */ public function paginate() { $condition = func_get_arg(0); $fileds = func_get_arg(1); $order = func_get_arg(2); $limit = func_get_arg(3); $page = func_get_arg(4); $recursive = func_get_arg(5); $extra = func_get_arg(6); // SQL文 $sql = $extra['extra']['type']; if (count($order) > 0) { $strOrderSql = ''; $numCnt = 0; foreach ($order as $key => $value) { $keys = explode('.', $key); $key = $keys[1]; if ($numCnt > 0) { $strOrderSql .= ',' . $key . ' ' . $value; } else { $strOrderSql .= $key . ' ' . $value; } $numCnt++; } $sql .= ' ORDER BY ' . $strOrderSql; } $sql .= ' LIMIT ' . $limit; if ($page > 1) { $sql .= ' OFFSET ' . ($limit * ($page - 1)); } return $this->query($sql); } /** * ページネート実行(カウント処理) * */ public function paginateCount() { $extra = func_get_arg(2); return count($this->query( preg_replace( '/LIMIT \d+ OFFSET \d+$/u', '', $extra['extra']['type'] ) )); } public function hasField($name, $checkVirtual = false) { return true; } }
ArticleExamination.phpという独自SQLを書いたもの
App::uses('PaginateOrigin', 'Model'); class ArticleExamination extends PaginateOrigin { public $name = 'ArticleExamination'; public $useTable = false; public function getList($in, $where) { $in = implode(',', $in); $strSql = <<< EOM SELECT teh.post_status AS post_status ,teh.working_on_flg AS working_on_flg ,teh.experience_id AS experience_id ,m_group.group_name AS group_name ,teh.post_title AS post_title ,user_info.nickname AS nickname ,teh.edit_time AS edit_time ,teh.plan_team_judgment_status AS plan_team_judgment_status ,plan_account.name AS plan_account_name ,teh.editor_team_judgment_status AS editor_team_judgment_status ,editor_account.name AS editor_account_name FROM t_experience_history AS teh LEFT OUTER JOIN m_group ON teh.post_group_id = m_group.id LEFT OUTER JOIN ( SELECT t_user.nickname AS nickname ,t_experience.id AS experience_id FROM t_user INNER JOIN t_experience ON t_user.id = t_experience.user_id ) AS user_info ON teh.experience_id = user_info.experience_id LEFT OUTER JOIN m_management_account AS plan_account ON teh.plan_team_judgment_account_id = plan_account.id LEFT OUTER JOIN m_management_account AS editor_account ON teh.editor_team_judgment_account_id = editor_account.id WHERE teh.post_status IN ({$in}) AND teh.delete_flg = 0 EOM; if ($where) { $strWhere = <<< EOM AND ( user_info.nickname LIKE '%{$where}%' OR teh.post_title LIKE '%{$where}%' OR teh.experience_id LIKE '%{$where}%' ) EOM; $strSql .= $strWhere; } return $strSql; } }
これで準備OK
さて本題のController
App::uses('AppController', 'Controller'); class ArticleExaminationController extends AppController { // レイアウト指定 public $layout = 'adminMainLayout'; public $uses = array('PaginateOrigin', 'ArticleExamination'); public $components = array('Admin', 'Paginator'); public function entryList() { $user = $this->Auth->user(); if (is_null($user)) { $this->redirect('/admin/'); } // 検索データがある場合、Where句に設定 $where = null; if ($this->data['search']) { $where = $this->data['search']; } // データ取得条件設定 switch ($this->request->params['poststatus']) { case 'examination': // 未審査・審査中 $in = array(TExperienceHistory::UNEXAMINED, TExperienceHistory::UNDER_EXAMINATION); break; case 'judgement': // 編集長判断待ち $in = array(TExperienceHistory::CHIEF_JUDGEMENT_WAITING); break; case 'reject': // 差し戻し $in = array(TExperienceHistory::REMAND_WAITING); break; case 'waiting': // 公開待ち $in = array(TExperienceHistory::PUBLIC_WAITING); break; case 'non_examination': // 公開済・差し戻し済 $in = array(TExperienceHistory::PUBLISHED, TExperienceHistory::REMAND_ALREADY); break; default: $this->redirect('/admin/dashboard/'); break; } // SQL $query = array( 'order' => array('teh.id' => 'asc'), 'limit' => 20, 'extra' => array( 'type' => $this->ArticleExamination->getList($in, $where), ), ); // ページャー設定 $this->Paginator->settings = $query; // データ取得 $data = $this->Paginator->paginate('PaginateOrigin'); // リストデータ $this->set('data', $data); // View設定 $this->render('/Admin/ArticleExamination/entry_list'); } この辺は関係ないので割愛 }
あ、あとViewね。これ大事
echo $this->Html->url(array('controller' => 'ArticleExamination', 'action' => 'entryList', 'poststatus' => $this->params['poststatus'])) . '/page:1/sort:PaginateOrigin.nickname/direction:asc';
こんな感じです
重要なのは、PaginateOrigin.phpの
public $virtualFields = array( 'teh_experience_id' => 'PaginateOrigin.experience_id', 'teh_nickname' => 'PaginateOrigin.nickname', 'teh_edit_time' => 'PaginateOrigin.edit_time', );
ですな。ソートで使いたいやつを宣言しておきます。
'一意になればなんでもいい' => 'モデル名.カラム名'
ってなってます。
コントローラのポイントは
$uses = array('PaginateOrigin', 'ArticleExamination');
です。PaginateOrigin($virtualFieldsを定義したModel)を一番左に書かないとソートうまく動かないんです。めんどくさい。
本当にざっとですみません。