CakePHPのSearch pluginでタグの絞込み検索を作る（AND検索）

CakePHP php

一昨日にOR検索のほうは修正したばかりですが、CakeDCのsearch pluginの記事が少ないので1個置いときますね。CakePHP Advent Calendar 2010 8日目 - kanonjiの日記のコードに機能追加しました。色々な検索の方法の中に、HABTMの検索が書いてありますが、これはOR検索です。HABTMの検索というと、分かりやすいのはタグ検索だけど、タグ検索なら絞込み検索も欲しいという事で、やってみました。

コード

<?php
    public $filterArgs = array(
        array('name' => 'tag_id_and', 'type' => 'subquery', 'field' => 'Entry.id', 'method' => 'subqueryByTagsAnd'),
    );
    public function subqueryByTagsAnd($data = array()){
        $this->EntriesTag->Behaviors->attach('Containable', array('autoFields' => false));
        $this->EntriesTag->Behaviors->attach('Search.Searchable');
        $tag_id = explode('|', $data['tag_id_and']);
        $options = array(
            'conditions' => array('tag_id'  => $tag_id),
            'fields' => array('entry_id'),
            'contain' => $this->Tag->alias,
        );
        if (( $c = count ( $tag_id )) !== 1 )
            $options['group'] = 'EntriesTag.entry_id HAVING COUNT(EntriesTag.entry_id) = '.$c;
        $condition = implode(', ', $this->EntriesTag->find('list', $options));
        if ( empty( $condition ))
            $condition = 'NULL';
        return $condition;
    }

AND検索のところだけ抜粋。Search pluginの使い方はCakeDCのsearch pluginの記事が少ないので1個置いときますね。CakePHP Advent Calendar 2010 8日目 - kanonjiの日記を見てください。

サンプル

https://github.com/kanonji/CakePHP-Search-plugin-sample/commit/568a479c8b4eb297be5d962a01956c1afdf8eafa
サンプルの方も修正してあります。
元々OR検索だけどタグ検索あったのに、サンプルデータに2個以上のタグがついたデータが無かったのでそれも追加しました。こういうアソシエーションをカバーしつつ、程よい件数なデータを、さくっと生成する方法ってないのかなぁ？

解説

SELECT * FROM posts_tags pt
LEFT JOIN posts p ON p.id = pt.post_id
LEFT JOIN tags t ON t.id = pt.tag_id
WHERE pt.tag_id IN (1,2,3)
GROUP BY pt.post_id
HAVING COUNT(pt.tag_id) = 3 /* 今回はタグを3つ指定しているので3ね */

「WHERE pt.tag_id IN (1,2,3)」の条件指定をしているので、その時点で取得してくる記事が持っているタグの数（COUNT(pt.tag_id)）の最大値は3ですよね。「HAVINGでCOUNT(pt.tag_id) = 最大値」で、すべてのタグを持っているレコードのみが取得できるというわけです。

[CakePHP]タグの絞込み機能 | HappyQuality

まずHABTMのAND検索は、このようにGROUP BY節とHAVING節を使うようです。以前にCakePHPじゃなくてオレオレフレームワークで作ってるときも、タグの絞り込み検索は作ったことがあったんですが、その頃はHABTMって考え方を知らなかったので、もっとトリッキーなSQLとテーブル構造でした。正直、あれはあまりいい方法とは言えなかった・・・
今回の方法は一応、http://k-brand.gr.jp/log/001734で同じ方法をとってるエントリーがもう1個あったので、多分いいんじゃないかなと、他に思いつかないし。

CakePHPでGROUP BY HAVINGを使う
'group' => 'Company.name HAVING count(Company.name)>= 2',
cakephp: HAVING句を無理矢理使う 重複しているデータを取りたい | 俺なんて….com ― ORENANTE.com ― ... orz

ちょっとバッドノウハウっぽいんですが、こう書けるみたいです。

$this->Model->find('all', array(
'group' => 'Photo.id HAVING count(Photo.rating) > 5'
));
http://www.tycamtech.com/php/cakephp/51-basics/125-mysql-and-the-qhavingq-expression-how-do-you-do-it-in-cakephp

英語のエントリーで同様の事を書いてるとこもありましたし、多分大丈夫だろうってことで。

一応、CakePHP 1.3.6のGROUP BYのとこの処理はDboSource::group()です。

サブクエリーにGROUP BY HAVINGはめちゃくちゃ重たい

サンプルで作ったのは、タグ検索以外もタイトルのLIKE検索や数値の範囲などを組み合わせて、エントリーを取得する検索です。なのでタグ検索部分をWHERE節のIN演算子にサブクエリーとして使います。これはOR検索と同様なんですが、サブクエリー内にHAVINGを使ったとたん、ありえないくらい重たくなってしまいました。MySQLでサブクエリーにHAVING使うと重たいってエントリーも見たけど、もしかしたら自分のデータベース設計のせいかもしれません。

<?php
$query = $this->EntriesTag->getQuery('all', $options);

最初はOR検索と同様に、これでサブクエリーを作ってました。

SELECT
    `Entry`.`id`,
    `Entry`.`user_id`,
    `Entry`.`label`,
    `Entry`.`body`,
    `Entry`.`pageview`,
    `Entry`.`is_running`,
    `Entry`.`created`,
    `Entry`.`modified`,
    `User`.`id`,
    `User`.`username`,
    `User`.`email`,
    `User`.`label`,
    `User`.`password`,
    `User`.`created`,
    `User`.`modified`
FROM
    `entries` AS `Entry`
    LEFT JOIN
    `users` AS `User` ON
    (`Entry`.`user_id` = `User`.`id`)
WHERE
    `Entry`.`id` IN
        (
        SELECT
            `EntriesTag`.`entry_id`
        FROM
            entries_tags AS EntriesTag
            LEFT JOIN
            tags AS Tag ON
            (`EntriesTag`.`tag_id` = `Tag`.`id`)
        WHERE
            tag_id IN (1,3)
        GROUP BY
            `EntriesTag`.`entry_id`
        HAVING
            COUNT(`EntriesTag`.`entry_id`) = 2
        ) AND
    `Entry`.`is_running` = 1 LIMIT 20

そうすると全体としてはこんなクエリーになります。サンプルアプリだと、件数が少ないから重たいって感じはしないけど、3000件くらいのアプリでやったら、OR検索で100ms程度に対して30000ms程度と、使い物にならない感じに。

<?php
$condition = implode(', ', $this->EntriesTag->find('list', $options));

仕方が無いので、サブクエリーの代わりとして先にクエリーを発行しました。

SELECT
    `EntriesTag`.`id`,
    `EntriesTag`.`entry_id`
FROM
    `entries_tags` AS `EntriesTag`
WHERE
    tag_id IN (1,3)
GROUP BY
    `EntriesTag`.`entry_id`
HAVING
    COUNT(`EntriesTag`.`entry_id`) = 2

SELECT
    `Entry`.`id`,
    `Entry`.`user_id`,
    `Entry`.`label`,
    `Entry`.`body`,
    `Entry`.`pageview`,
    `Entry`.`is_running`,
    `Entry`.`created`,
    `Entry`.`modified`,
    `User`.`id`,
    `User`.`username`,
    `User`.`email`,
    `User`.`label`,
    `User`.`password`,
    `User`.`created`,
    `User`.`modified`
FROM
    `entries` AS `Entry`
    LEFT JOIN
    `users` AS `User` ON
    (`Entry`.`user_id` = `User`.`id`)
WHERE
    `Entry`.`id` IN (8,17,26) AND
    `Entry`.`is_running` = 1 LIMIT 20

クエリーはこんな感じで、WHERE IN(Id列挙)になって、3000件くらいのだと100ms+100msくらいに落ち着きました。
$filterArgsで'type' => 'subquery'を使うと、Search plugin側で`Entry`.`id` IN ()までは組み立ててしまいます。なのでsubqueryByTagsAnd()の戻り値として、サブクエリーの代わりにIdを列挙した文字列でも通るって事でした。

環境

Mac Mac OS X 10.5.8（Leopard）
MAMP 1.7.2
CakePHP 1.3.6
php 5.2.6
MySQL 5.0.41
CakeDC Search plugin updating readme · CakeDC/search@668eb68 · GitHub