6月 07
24.検索とPaginationとHABTM
で、さらにやっかいなのが、ここに「好きな物」のチェックボックスの複数選択検索を入れる場合です。
HABTMの関連テーブルですね。
一体どうすりゃいいんでしょうか。ということで相当悩みました。
最初にWEBで調べて結果は以下のようになった。
まず、 members_controller.php に、
public $paginate = array('Member' => array(
'limit' => 5,
'order' => array(
'Member.id' => 'desc'
),
'joins' => array(
array(
'table' => 'members_favorites',
'alias' => 'MembersFavorite',
'type' => 'left',
'conditions'=> array('MembersFavorite.member_id = Member.id')
),
)
));
こうすると members テーブルと members_favorites テーブルが LEFT JOIN してくれる。
LEFT JOIN するだけなので、当然、members 側のデータがだぶってしまう（好きな物は複数選択だから）。
ので、 DISTINCT を使って重複を取り除きたい、ってことで、上記に以下を追加。(PostgreSQLの場合）
'fields'=>array('DISTINCT ("Member".id) AS "Member__id"',
'"Member"."email" AS "Member__email"',
'"Member"."password" AS "Member__password"',
'"Member"."type_id" AS "Member__type_id"',
'"Member"."birthday" AS "Member__birthday"',
'"Member"."created" AS "Member__created"',
'"Type"."id" AS "Type__id"',
'"Type"."name" AS "Type__name"'
),
これで検索結果はちゃんと出るようになるんだけど、 pagination で使う「全件数」と数が合わなくなるので、今度はmembers のモデル member.php に、以下を追加。
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
$sql = 'SELECT DISTINCT("Member".id) FROM "members" AS "Member" left JOIN members_favorites AS "MembersFavorite" ON ("MembersFavorite"."member_id" = "Member"."id") LEFT JOIN "types" AS "Type" ON ("Member"."type_id" = "Type"."id") WHERE 1 = 1';
$this->recursive = $recursive;
$results = $this->query($sql);
return count($results);
}
これでオリジナルの pagination のカウント処理が上書きされるので、正しい数字が出るようになる。
ただし、先ほどのDISTINCTするための設定
'fields'=>array('DISTINCT ("Member".id) AS "Member__id"',
'"Member"."email" AS "Member__email"',
'"Member"."password" AS "Member__password"',
'"Member"."type_id" AS "Member__type_id"',
'"Member"."birthday" AS "Member__birthday"',
'"Member"."created" AS "Member__created"',
'"Type"."id" AS "Type__id"',
'"Type"."name" AS "Type__name"'
),
これが PostgreSQL と MySQL で書き方を変えなきゃいけない。
いろいろやってみたけど、
PostgreSQL が、
'DISTINCT ("Member".id) AS "Member__id"',
MySQL が
'DISTINCT (Member.id)',
とするのが精一杯。
だけど全くこんなのナンセンスだ。
思ったのは DISTINCT がガンなんだよね、これを使う限り実現できない。
でも複数選択なんだからしょうがないじゃん、とは思うが。
ということで、根本的にデータの引っ張り方を変えてみることにした。
要は今テーブルを連結してその結果を DISTINCT してるのがいけないわけだから、一旦その「好きな物」を選択している会員の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'),
'fields' => array('MembersFavorite.member_id'),
'conditions' => array('MembersFavorite.favorite_id' => $favorite_id)
));
$conditions = array("Member.id" => $fid);
$data = $this->paginate('Member', $conditions);
で、これ会員が10万人とかになったらどうなるの？ってことで。。。
整理してみる。
最初に考えたのが members と members_favorites を LEFT JOIN して、結果を DISTINCT してしまおうというやつね。イメージとしてはこんな感じ。
SELECT DISTINCT(m.id) FROM members m LEFT JOIN members_favorites mf ON m.id=mf.member_id WHERE mf.favorite_id IN (1,2);
もう一つが一旦 members_favorites から member_id を抽出して、その結果のリストを IN に突っ込んでしまおうというもの。
SELECT m.id FROM members m
WHERE m.id IN
(SELECT member_id
FROM members_favorites mf
WHERE mf.favorite_id IN (1,2)
);
これは上記ではIN ()の中はSELECT文になっているが、CakePHPの文法だとそれは入らないようなので、実際に配列が入る。こんな感じ。
IN (1,3,4,7,9,11,12,15,16)
でどっちがパフォーマンスいいの？ってことでデータを2万件くらい入れてテストしてみた。
結果、SQL単体で見たらIN方式の方が全然速い。
（たとえIN()の中にリストが1万個入っていても）
けれどもCakePHPのデバッグ画面に出るトータルの処理時間を見ると若干JOIN方式の方が速いかなって印象だけどまあ恐らく誤差範囲。ほぼ同じとみていいと思う。
ということでIN方式で決定。
最終的にこうなりました。
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']) = split("-", $v);
}elseif($k == 'to'){
list($t['year'],$t['month'],$t['day']) = split("-", $v);
}elseif($k == 'email'){
$email = urldecode($v);
}elseif($k == 'type_id'){
$type_id = urldecode($v);
}elseif(ereg("^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'),
'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'));
}