Yii 1.1 で CActiveRecord や CActiveDataProvider を使っているときに、HAS_MANY のリレーションによる検索をうまく書くことが出来ずに、途方に暮れてしまう場合があります。この記事では、よくある課題を解きながら、どうやって HAS_MANY のリレーションによる検索を書いたらよいかを見ていきます。
この記事は Yii 1.1 のみを対象としたものです。Yii 2 については、演習 - HAS_MANY リレーションによる検索 (Yii 2 版) を参照して下さい。
リレーション
二つのエンティティが 1:N の関係になる場合があります。というか、普通のデータベースを使う限りにおいては、この関係が唯一可能な関係で、1:1 の関係は 1:N の特殊形、N:N の関係は複数の 1:N の組合せとして考えることが出来ます。
で、この 1:N のありふれた関係を Yii の ORM である CActiveRecord でも、BELONG_TO および HAS_MANY のリレーションとしてサポートしています。1:N の N の側から見れば BELONGS_TO であり、1 の側から見れば HAS_MANY ということですな。
BELONGS_TO については、特に難しい事はありません。問題なし。
では、最初に、次のような関係の二つのオブジェクトを仮設しましょう。
例 : 投稿者と記事
投稿者(Author) と 記事(Post) の関係を考えて下さい。An Author has many Posts であり、A Post belongs to an Author であるという関係ですね。
<?php
/**
* 投稿者のモデル
* @property integer $id
* @property string $name 投稿者名
...
*/
class Author extends CActiveRecord
{
...
public function relations()
{
return array(
'posts' => array(self::HAS_MANY, 'Post', 'author_id');
);
}
...
<?php
/**
* 記事のモデル
* @property integer $id
* @property integer $author_id 投稿者 id への FK
* @property string $title タイトル
...
*/
class Post extends CActiveRecord
{
...
public function relations()
{
return array(
'author' => array(self::BELONGS_TO, 'Author', 'author_id');
);
}
...
まあ、こんな感じかな。で、この例を使って、よくある課題を解決する方法を考えてみよう、という訳です。
課題 1
ある言葉をタイトルに含む記事を列挙せよ
まずは肩慣らし。ある言葉をタイトルに含む記事を取得してみましょう。
<?php
...
public static function GetPostsByTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// タイトルを比較
$criteria->compare('title', $searchWord, true);
// 取得
$posts = Post::model()->findAll($criteria);
// 表示
foreach($posts as $post)
{
echo "Title = " . $post->title . "\n";
}
}
あ、簡単すぎたか。というか、そもそも、リレーションを使っていない。
はい、次。
課題 2
ある言葉をタイトルに含む記事を投稿者名とともに列挙せよ
今度は、投稿者名も一緒に取得しなさい、と。
<?php
...
public static function GetPostsWithAuthorByTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Author を一緒に取得
$criteria->with = array('author');
// タイトルを比較
$criteria->compare('t.title', $searchWord, true);
// 取得
$posts = Post::model()->findAll($criteria);
// 表示
foreach($posts as $post)
{
echo "Title = " . $post->title . "\n";
echo "Author = " . $post->author->name . "\n";
}
}
これも簡単でしたね。
あれ？「そんなことしなくても、課題 1 のコードに $post->author->name を echo する文を加えたら良いだけじゃないの」と言った人、誰ですか？
そう、あなたは正しい。Post モデルには author というリレーションが定義されているので、$post->author とやりさえすれば、関連する Author を勝手に取ってきてくれます。これ、便利ですね。いわゆる レイジーローディング という奴です。
しかし レイジーローディング だと、この場合、Post の配列を取得するのに一回、それぞれの Author を取得するのに Post の個数分、合計 1+N 個のクエリが実行されることになります。
それでは効率が悪いだろう、ということで、ここでは CDbCriteria::with を使って、Post と Author を一緒に取得しています。これだと、実行されるクエリはたった一回で済みます。つまり、テーブルを結合したクエリを実行してくれる。これがいわゆる イーガーローディング です。
ちなみに、どんなときでも イーガーローディング の方が効率的で望ましいかというと、そういう訳でもないので、要注意です。
コードの中で注意すべきは、with を使ってテーブルを結合した場合は、カラム名をテーブル名エイリアスで修飾して、曖昧さをなくす必要がある、ということです。
主テーブルに対しては t という固定のエイリアスを使います。リレーション・テーブルのエイリアスは、リレーション名そのものを使います。
従って、投稿者名で検索したい場合は次のようになります。
<?php
...
// 投稿者名を比較
$criteria->compare('author.name', $searchName, true);
うーん、簡単だなあ。Yii の CActiveRecord って良く出来てるわ。便利だわ。
「そんな事より、おまえ、HAS_MANY はどうなった？」とか言って怒っている人、誰ですか？
そう、あなたはこれ以上ないぐらいに正しい。これは、まだ、リレーションと言っても簡単な方の BELONGS_TO の話でした。済みません。じゃ、いよいよ本題。
課題 3
ある言葉をタイトルに含む記事を書いた投稿者を列挙せよ
記事じゃなく、投稿者の方を取得しなさい、ということです。
<?php
...
public static function GetAuthorsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post を一緒に取得
$criteria->with = array('posts');
// タイトルを比較
$criteria->compare('posts.title', $searchWord, true);
// 取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
}
}
ん？
これで良いのかな？ いや、良いはずだけど、何か、簡単すぎるな。ま、いいか。次。
課題 4
ある言葉をタイトルに含む記事を書いた投稿者をその全記事とともに列挙せよ
ふむふむ、課題 3 にちょっと追加すりゃ良いのか。とりあえず、こうかな？
ひっかかった解答
<?php
...
public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post を一緒に取得
$criteria->with = array('posts');
// タイトルを比較
$criteria->compare('posts.title', $searchWord, true);
// 取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post)
{
echo "Post = " . $post->title . "\n";
}
}
}
ブー!! ひっかかった。
何で？
もし課題が「ある言葉をタイトルに含む記事を書いた投稿者を 該当する記事とともに 列挙せよ」であれば、これで良いんです。けれども、課題は「全記事とともに」だから、これでは駄目。
つまり、記事のタイトルで検索しているために、検索対象の言葉をタイトルに含まない記事が漏れてしまうんですな。
じゃあ、イーガーローディングをやめてレイジーローディングにすれば良いじゃないか、という話なのだけれども、記事のタイトルを検索するためには post テーブルを with で結合しない訳には行かない。
どうするか？
解答
<?php
...
public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post を結合する（が取得しない）
$criteria->with = array(
'posts' => array(
'select' => false,
),
);
// タイトルを比較
$criteria->compare('posts.title', $searchWord, true);
// 取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post)
{
echo "Post = " . $post->title . "\n";
}
}
}
リレーションの定義で select を false にしてやると、テーブルは結合するけれども、その値は取得しない、という動作にすることが出来ます。
これで Post をレイジーローディングすることが可能になる訳ですな。
また、リレーションの定義は、上記のように、その場その場で動的に変更することが可能です。
では、次。これはちょっと難しいですよ。
課題 5
ある言葉をタイトルに含む記事を書いた投稿者をその全記事とともに名前順で5人まで表示せよ
まあ、とりあえず、ORDER と LIMIT を追加してみます。
とりあえずの解答
<?php
...
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post を一緒に取得
$criteria->with = array('posts');
// タイトルを比較
$criteria->compare('posts.title', $searchWord, true);
// Author の名前でソート
$criteria->order = 't.name ASC';
// Author は5人まで
$criteria->limit = 5;
// 取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post)
{
echo "Post = " . $post->title . "\n";
}
}
}
これは、そもそも、$searchWord が空っぽでない限り、エラーになります。理不尽にも「posts.title なんていうカラム無いぞ」と怒られる。何で？ 'with' に 'posts' を指定しているのに、何で？訳が分らん。
ガイドに曰く
デフォルトでは、主たるモデルに対して LIMIT が適用されない限り、単一の SQL 文を生成して、イーガーローディングを実行します。
つまり、逆に言うと、主たるモデルに対して LIMIT が適用される場合はレイジーローディングになる、という事です。上記の解答でも limit を指定しているため、勝手に レイジーローディング に切り替ってしまいます。その結果、テーブルが結合されないままクエリが実行されてエラーになるという訳です。
どうすれば良いか。
さらにガイドに曰く
リレーションの宣言において together オプションを true に設定すれば、LIMIT が使用される場合であっても、単一の SQL 文を生成するように強制することが可能です。
という訳で、解答を修正します。
解答修正案その1
<?php
...
// Post の結合を強制する
$criteria->with = array(
'posts' => array(
'together' => true,
),
);
...
どうだ？ 今度はエラーは出ないだろう。
が、しかし、表示される結果がおかしいでしょう？ 例えば、こんな出力結果になりますよね。
[検索語 = foo]
投稿者 = ぴよ
記事 = foo の効用
記事 = 私は foo が好きだ
投稿者 = ふが
記事 = foo って何よ
投稿者 = ほげ
記事 = foo は使うな
記事 = foo の代りに hoge だ
[以上]
つまり、投稿者を5人まで表示したいのに、記事の合計数が5になる所でリストがちょん切れてしまうのです。
ははーん、じゃあ、また 'select' => false の手を使うか、と。
解答修正案その2
<?php
...
// Post の結合を強制する（が、取得はしない）
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
...
残念ながら、これも駄目。こんな出力結果になります。
[検索語 = foo]
投稿者 = ぴよ
記事 = foo の効用
記事 = 私は foo が好きだ
記事 = 私は bar も好きだ
投稿者 = ふが
記事 = foo って何よ
記事 = bar って何よ
投稿者 = ほげ
記事 = foo は使うな
記事 = foo の代りに hoge だ
記事 = bar は使うな
記事 = bar の代りに piyo だ
[以上]
投稿者を5人取得したいのに、3人しか取得できません。LIMIT が主テーブルじゃなく、結合された結果としての仮想的なテーブルに適用されている結果、そこに含まれている投稿者しか拾ってこれない訳ですね。まあ、リレーショナルデータベースのクエリって、そういうもんですわな。仕方がない。
いや、諦めずに、もう一ひねり、グループ化 してみましょう。
解答修正案その3
<?php
...
// Post の結合を強制する（が、取得はしない）
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
...
// Author の ID でグループ化する
$criteria->group = 't.id';
...
おお、出来た。出来てしまった。
まとめておきます。
解答
<?php
...
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post の結合を強制する（が、取得はしない）
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
// タイトルを比較
$criteria->compare('posts.title', $searchWord, true);
// Author の ID でグループ化する
$criteria->group = 't.id';
// Author の名前でソート
$criteria->order = 't.name ASC';
// Author は5人まで
$criteria->limit = 5;
// 取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post)
{
echo "Post = " . $post->title . "\n";
}
}
}
では、最後の課題。
課題 6
ある言葉をタイトルに含む記事を書いた投稿者を該当する記事とともに名前順で5人まで表示せよ
これはもう、レイジーローディングの時にもう一度同じフィルターをかけるしか、方法は無いんじゃないかな。
ということで、書いてみます。
解答例
<?php
...
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post の結合を強制する（が、取得はしない）
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
// タイトルを比較
$criteria->compare('posts.title', $searchWord, true);
// Author の ID でグループ化する
$criteria->group = 't.id';
// Author の名前でソート
$criteria->order = 't.name ASC';
// Author は5人まで
$criteria->limit = 5;
// 取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
// フィルターをかけて記事をレイジーローディングで取得する
$filteredPosts = $author->posts(
array(
'condition' => 'title LIKE :search_word',
'params' => array(
':search_word' => '%' . $searchWord . '%',
),
)
);
foreach($filteredPosts as $post)
{
echo "Post = " . $post->title . "\n";
}
}
}
エレガントとは言えないまでも、トリッキーなことをせずに、正攻法で解決できました。
レイジーローディングの時に条件を指定できる、というのがこの解答の味噌です。
ガイドに曰く
動的なクエリオプションは、レイジーローディングのアプローチを使ってリレーショナルクエリを実行するときにも、使用できます。そうするためには、リレーション名と同じ名前のメソッドを、パラメータに動的なクエリオプションを指定して、呼び出します。
と。
まとめ
うーむ、結局、最後まで途方に暮れることなく、何とかなってしまった。
当初は、HAS_MANY で検索するときのジレンマとその本質的な原因 ... 検索するためには JOIN が必要だが JOIN すると主たるオブジェクトの数が狂う ... を分りやすく示すことが出来れば、それで十分だと思っていました。
Yii の CActiveRecord って、ふだん思っている以上に、結構よく出来てるなあと改めて思った次第です。
CActiveDataProvider
ここでは触れませんでしたが、CActiveDataProvider を使って検索する場合でも、本質的に事情は同じです（レイジーローディングの時に条件を指定するのはちょっと面倒臭いかもしれない）。
CActiveDataProvider を使う状況では、CPagination を一緒に使うのが普通です。鬱陶しい LIMIT が常にそこにあって邪魔をすると考えて下さい。
Yii 公式ガイド : リレーショナルアクティブレコード
上記で「ガイド」と呼んでいるのは、Yii の公式ガイドです。日本語訳もありますよ。この記事で参照しているのは、「リレーショナルアクティブレコード」です。
ややこしい所ではあるので、一通り理解した後でも、ときどき読み返してみるのがお奨めです。
追記: 検索専用のリレーションを使うと良い
今頃（三ヶ月後）になって気付きました。
検索専用のリレーションを定義すれば、同じテーブルを検索用とデータ取得用に分けてそれぞれ独立に結合することが可能になります。.
例えば、課題 4 に対する解答は以下のように書くことが出来ます。
課題 4 に対する最適化された解答
<?php
...
public function relations()
{
return array(
'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
'posts_search' => array(self::HAS_MANY, 'Post', 'author_id'),
);
}
public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post モデルを結合する（一つはデータ取得用、もう一つは検索用）
$criteria->with = array(
'posts' => array( // データ取得用
'together' => false,
),
'posts_search' => array( // 検索用
'select' => false,
'together' => true,
),
);
// title を比較
$criteria->compare('posts_search.title', $searchWord, true);
// Author とその Post を取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post) // ここでクエリは発行されない !!
{
echo "Post = " . $post->title . "\n";
}
}
}
前の解答とこの最適化された解答との違いは、パフォーマンス にあります。
前の解答では、1 + N 個のクエリが実行されました。なぜなら、$author->posts と書いて posts にアクセスするたびに、posts リレーションを取得するためのクエリが実行されるからです。
しかし、新しい解答では 2 個のクエリしか実行されません。$author->posts と書いて posts にアクセスしても、posts リレーションの内容は findAll で取得済であるため、新たなクエリは発行されません。あ、そうです。findAll は 2 個のクエリを実行します。一つ目のクエリは、メイン・テーブルおよび together を指定されたリレーションのデータを取得します。そして二つ目は together が false であるリレーションのデータを取得します。（Yii は、二つ目のクエリでは、最初のクエリで取得したプライマリ・キーを IN の条件として使用して、取得するデータをフィルタリングしています。）
同様に、課題 5 と課題 6 の解答も、下記のように最適化することが可能です。:
課題 5 に対する最適化された解答
<?php
...
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
// クエリ基準
$criteria = new CDbCriteria();
// Post モデルを結合する（一つはデータ取得用、もう一つは検索用）
$criteria->with = array(
'posts' => array( // データ取得用
'together' => false,
),
'posts_search' => array( // 検索用
'select' => false,
'together' => true,
),
);
// Author の ID でグループ化する
$criteria->group = 't.id';
// Author の名前でソート
$criteria->order = 't.name ASC';
// Author は5人まで
$criteria->limit = 5;
// Author とその Post を取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post) // ここでクエリは発行されない !!
{
echo "Post = " . $post->title . "\n";
}
}
}
課題 6 に対する最適化された解答
<?php
...
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
// Post モデルを結合する（一つはデータ取得用、もう一つは検索用）
$criteria->with = array(
'posts' => array( // データ取得用
'together' => false,
'condition' => 'posts.title LIKE :search_word',
'params' => array(
':search_word' => '%' . $searchWord . '%',
),
),
'posts_search' => array( // 検索用
'select' => false,
'together' => true,
),
);
// Author の ID でグループ化する
$criteria->group = 't.id';
// Author の名前でソート
$criteria->order = 't.name ASC';
// Author は5人まで
$criteria->limit = 5;
// Author とその Post を取得
$authors = Author::model()->findAll($criteria);
// 表示
foreach($authors as $author)
{
echo "Author = " . $author->name . "\n";
foreach($author->posts as $post) // ここでクエリは発行されない !!
{
echo "Post = " . $post->title . "\n";
}
}
}