検索とPaginationとHABTM
で、さらにやっかいなのが、ここに「好きな物」のチェックボックスの複数選択検索を入れる場合です。 HABTMの関連テーブルですね。 一体どうすりゃいいんでしょうか。ということで相当悩みました。 結論としては、一旦その「好きな物」を選択している会員のIDを全部取り出して、そいつを条件に入れちゃえばいいんじゃなかろうかと。 クエリーのイメージはこんな感じ。
SELECT m.id FROM members m WHERE m.id IN (SELECT member_id FROM members_favorites mf WHERE mf.favorite_id IN (1,2));
つまり()内のSELECTの部分をあらかじめデータ取得しておけばいいと。 ということでこうなった。
$fid = $this->Member->MembersFavorite->find('list', array(
'order'=>array('MembersFavorite.member_id DESC'),
'group'=>array('MembersFavorite.member_id'),
'fields' => array('MembersFavorite.member_id'),
'conditions' => array('MembersFavorite.favorite_id' => $favorite_id)
));
$conditions = array("Member.id" => $fid);
$data = $this->paginate('Member', $conditions);
これは上記ではIN ()の中はSELECT文になっているが、CakePHPの文法だとそれは入らないようなので、実際に配列が入る。こんな感じになる。
SELECT `MembersFavorite`.`id`, `MembersFavorite`.`member_id` FROM `members_favorites` AS `MembersFavorite` WHERE `MembersFavorite`.`favorite_id` IN (1, 2, 3) GROUP BY `MembersFavorite`.`member_id` ORDER BY `MembersFavorite`.`member_id` DESC
最終的にこうなりました。
function admin_search() {
$this->Member->recursive = 0;
if (!empty($this->data)) {
$f = $this->data['Member']['from'];
$t = $this->data['Member']['to'];
$email = $this->data['Member']['email'];
$type_id = $this->data['Member']['type_id'];
$favorite_id = $this->data['Member']['favorites'];
}else{
foreach ($this->passedArgs as $k => $v){
if ($k == 'from'){
list($f['year'], $f['month'], $f['day']) = preg_split("/-/", $v);
}elseif($k == 'to'){
list($t['year'], $t['month'], $t['day']) = preg_split("/-/", $v);
}elseif($k == 'email'){
$email = urldecode($v);
}elseif($k == 'type_id'){
$type_id = urldecode($v);
} elseif(preg_match("/^favorite_id_([0-9]+)$/", $k, $regs)) {
$favorite_id[$regs[1]] = $v;
}
}
}
if (isset($f) && isset($t)){
if ($this->_from_to_check($f, $t)) {
$from = $f['year']."-".$f['month']."-".$f['day'];
$to = $t['year']."-".$t['month']."-".$t['day'];
$this->data['Member']['from'] = $f;
$this->data['Member']['to'] = $t;
}
}
if(isset($email)){
$this->data['Member']['email'] = $email;
}
if(isset($type_id)){
$this->data['Member']['type_id'] = $type_id;
}
if (isset($favorite_id)){
$this->data['Member']['favorites'] = $favorite_id;
}
$searchword = array();
$conditions = array();
if (isset($from) && isset($to)){
$searchword = array(
"from" => urlencode($from),
"to" => urlencode($to),
);
$conditions = array("Member.created BETWEEN ? AND ?" => array($from,$to));
}
if (isset($email) && $email){
$searchword = $searchword + array(
"email" => urlencode("$email"),
);
$conditions = $conditions + array("Member.email LIKE" => "%$email%");
}
if (isset($type_id) && $type_id){
$searchword = $searchword + array(
"type_id" => urlencode("$type_id"),
);
$conditions = $conditions + array("Member.type_id" => $type_id);
}
if (isset($favorite_id) && $favorite_id){
foreach ($favorite_id as $k => $v){
$temp_favotite['favorite_id_'.$k] = $v;
}
$searchword = $searchword + $temp_favotite;
$fid = $this->Member->MembersFavorite->find('list', array(
'order'=>array('MembersFavorite.member_id DESC'),
'group'=>array('MembersFavorite.member_id'),
'fields' => array('MembersFavorite.member_id'),
'conditions' => array('MembersFavorite.favorite_id' => $favorite_id)
));
$conditions = $conditions + array("Member.id" => $fid);
}
$data = $this->paginate('Member', $conditions);
$this->set('searchword', $searchword);
$this->set("members", $data);
$favorites = $this->Member->Favorite->find('list');
$types = $this->Member->Type->find('list');
$this->set(compact('favorites','types'));
}
