LoginSignup
6
5

More than 5 years have passed since last update.

Yii2Advent Calendar 2014Day 6
@tanakahisateru(田中 ひさてる)

ActiveRecordのJOINにテーブルエイリアスを使う

Last updated at Posted at 2014-12-06

Yii2アドベントカレンダー6日目の記事です。今日から4連発の予定なのにいきなり飛ばします。どうなるかな。

この記事は、とにかく主テーブルのエイリアス指定は ->from(['alias' => 'table_name']) だというのを憶えれば80%言いたいことは伝わっています。と前置きをしておいて、と。

単一テーブルの場合

Yii2 の目玉はなんといってもデータベースの操作が洗練されたことでしょう。たとえば、SourceCode クラスで表される「ソースコード」テーブルのすべてを順に得たい場合はこうで、作られる SQL はこうです。

$query = SourceCode::find()->orderBy('created_at');
$query->all();

SELECT *
 FROM `source_code`
 ORDER BY `created_at`

(以降も、PHP のコードに続けて SQL を書くというパターンで書きます)

Yii1 と違い、2 では暗黙のテーブルエイリアスが付かない、素朴なクエリを出力するようになりました。エイリアスがどうしても必要になる問題が起こる率は、そうでない率と比べてかなり少ないので、デフォルトはエイリアスなし、というのが Yii2 の方針です。そのほうが、ActiveQuery (ActiveRecord のクエリビルダ) との一貫性が高く、また MongoDB の ActiveRecord との相似性が高くなります。(MongoDB にはそもそも JOIN がありません)

昨今の Web アプリケーションでは、複雑なクエリ1本でデータベースに仕事をさせるよりも、データベースへの問い合わせをなるべくキーバリューストア的にアクセスして、スケールアウトできるアプリケーション側がキャッシュを駆使してがんばる、というトレンドがあることも、このデフォルトエイリアスなし方針と関係あるかもしれません。

単純なリレーションの場合

SourceCode は Programmer クラスで表される作者 author を持ちます。これをクラス実装で表すと次のようになります。

class SourceCode extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'source_code';
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getAuthor()
    {
        return $this->hasOne(Programmer::className(), ['id' => 'author_id']);
    }
}

class Programmer extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'programmer';
    }
}

一定以上のスキルを持つ作者のソースコードだけが欲しいときは、こんなふうにリレーション定義を利用して JOIN すれば、あとは ->where(...) でなんとかなりそうです。お手軽ですね。

$query = SourceCode::find()->joinWith('author');
$query->where(...);

SELECT `source_code`.*
 FROM `source_code`
 LEFT JOIN `programmer`
  ON `source_code`.`author_id` = `programmer`.`id`
 WHERE ...

ただ、2つのテーブル間でカラム名の競合が起こる場合は、その WHERE の中で、 テーブル名 を使ったカラム名指定が必要になります。ちょっと ORM で DB を隠蔽できていない感じですね。

$query->where([
    'source_code.stared' => true,
    'programmer.stared' => true,
]);

これが気に入らない人は、こう書くこともできます。

$query->where([
    SourceCode::tabelName() . '.stared' => true,
    Programmer::tabelName() . '.stared' => true,
]);

まだどうしてもエイリアスが必要というわけではありません。

複数のリレーションが同じテーブルを参照するケース

SourceCode が作者 author を持ちつつ、同じ Programmer クラスであるレビュアー reviewer を持つ場合を考えてみましょう。

class SourceCode extends \yii\db\ActiveRecord
{
    /**
     * @return \yii\db\ActiveQuery
     */
    public function getAuthor()
    {
        return $this->hasOne(Programmer::className(), ['id' => 'author_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getReviewer()
    {
        return $this->hasOne(Programmer::className(), ['id' => 'reviewer_id']);
    }
}

さきほどの作者の属性による抽出に、さらに「レビュアーが一定以上の評価を持つ場合でもよい」という条件が加わるとどうでしょうか。複数リレーションの JOIN はこうです。

$query = SourceCode::find()->joinWith(['author', 'reviewer']);

SELECT `source_code`.*
 FROM `source_code`
 LEFT JOIN `programmer`
  ON `source_code`.`author_id` = `programmer`.`id`
 LEFT JOIN `programmer`
  ON `source_code`.`reviewer_id` = `programmer`.`id`

おかしいですね。同じテーブルをそのままの名前で2つ結合しています。テーブル名でカラム指定を区別できなくなってしまいました。

Yii2 ではプレーンなクエリと ActiveRecord のクエリがうまく統合されています。そのおかげで、リレーション定義と無関係に自由な JOIN をすることも自然に可能です。

 $query = SourceCode::find()
    ->leftJoin(Programmer::tableName(), 'programmer.id = source_code.author_id')
    ->leftJoin(Programmer::tableName(), 'programmer.id = source_code.reviewer_id');

SELECT `source_code`.*
 FROM `source_code`
 LEFT JOIN `programmer`
  ON programmer.id = source_code.author_id
 LEFT JOIN `programmer`
  ON programmer.id = source_code.reviewer_id

結合条件を文字列で指定したので、クオートの有無が変わってしまいましたが、意味は上の重複したテーブル結合と同じです。

ここではぜひ、テーブル名文字列ではなく ActiveRecord::tableName() を使って下さい。もし IDE を使っていれば、キー入力のほとんどは補完されます。タイポしたら IDE がなにか警告してくれるかもしれません。また、IDE がなくても、こう書いてあれば、タイポが間違った SQL の実行ではなく、PHP コードの評価失敗になってくれます。

SQL に詳しい人は、そろそろテーブルにエイリアスを指定しないといけないことに気づいているでしょう。プレーンな JOIN におけるエイリアスの指定はこうです。キーと値がそれぞれエイリアスとテーブルを指します。

$query = SourceCode::find()
    ->leftJoin(['author' => Programmer::tableName()], 'author.id = source_code.author_id')
    ->leftJoin(['reviewer' => Programmer::tableName()], 'reviewer.id = source_code.reviewer_id');

SELECT `source_code`.*
 FROM `source_code`
 LEFT JOIN `programmer` `author`
  ON author.id = source_code.author_id
 LEFT JOIN `programmer` `reviewer`
  ON reviewer.id = source_code.reviewer_id

これで、それぞれの結合が表すテーブルに author と reviewer という名前が付きました。この名前を使って、それぞれの関係でどうあって欲しいかを以降の WHERE に書くことができます。

複数のリレーションが同じテーブルを参照する時もリレーション定義を使いたい

けれど、せっかくリレーションを定義したのだから、なんとかそれを利用したいものです。どの外部キーで結合しているかを知らないと、クライアントコードが書けない、というのは、うまく知識を隠蔽したいですね。

同じ要領でいけるなら、次のコードでリレーション版ができそうな気もしますが、残念ながらうまくいきません。

// これは動かない
$query = SourceCode::find()->joinWith(
    ['author' => 'author']
)->joinWith(
    ['reviewer' => 'reviewer']
);
// PHP Warning 'yii\base\ErrorException' with message 'call_user_func() expects parameter 1 to be a valid callback, function 'author' not found or invalid function name'

joinWith の結合を表す部分の配列は、「リレーション名とリレーションクエリを加工するコールバック」なのです。つまり、そのコールバックを使って hasMany() の戻り値である ActiveQuery のインスタンスを加工します。どう加工するかというと、クエリの主となるテーブルのエイリアスを指定するために、 ->from(['alias' => 'table_name']) を使います。

$query = SourceCode::find()->joinWith([
    'author' => function(ActiveQuery $q) {
        $q->from(['author' => Programmer::tableName()]);
    },
    'reviewer' => function(ActiveQuery $q) {
        $q->from(['reviewer' => Programmer::tableName()]);
    },
]);

SELECT `source_code`.*
 FROM `source_code`
 LEFT JOIN `programmer` `author`
  ON `source_code`.`author_id` = `author`.`id`
 LEFT JOIN `programmer` `reviewer`
  ON `source_code`.`reviewer_id` = `reviewer`.`id`

素朴な書き方をしているため PHP は冗長ですが、これでテーブル名と外部キー定義を知ることなく、望んだ SQL を得ることができました。

ちなみに $q->modelClass というプロパティには Programmer の完全修飾名が入っています。クラス名さえも直接指定したくない場合は、それを使ってもかまいません。

'author' => function(ActiveQuery $q) {
    $className = $q->modelClass;
    $q->from(['author' => $className::tableName()]);
},

あとは自由に WHERE をつなげればよいでしょう。

構文は複雑に見えますが、SQL の文字列結合ではないので、この冗長なコードはいくらでもリファクタリングできます。

同じテーブルにリレーションを持つ場合

GitHub の Gist では、単独のソースコードでも別の作者が手元にフォークすることができます。もしソースコードが、同じテーブルである SourceCode クラスを参照する forkOrigin を持っていた場合はどうでしょうか。

class SourceCode extends \yii\db\ActiveRecord
{
    /**
     * @return \yii\db\ActiveQuery
     */
    public function getForkOrigin()
    {
        return $this->hasOne(SourceCode::className(), ['id' => 'fork_origin_id']);
    }
}

単独のリレーションなので簡単にできるとおもいきや...

$query = SourceCode::find()->joinWith('forkOrigin');

SELECT `source_code`.*
 FROM `source_code`
 LEFT JOIN `source_code`
  ON `source_code`.`fork_origin_id` = `source_code`.`id`

いきなりおかしいですね。JOIN したいテーブ名が FROM と競合します。両方にエイリアスを指定する必要があります。ここでもやはり、基本のお作法は ->from(['alias' => 'table_name']) です。

$query = SourceCode::find()
    ->from(['code' => SourceCode::tableName()])
    ->joinWith([
        'forkOrigin' => function(ActiveQuery $q) {
            $q->from(['origin' => SourceCode::tableName()]);
        },
    ]);

SELECT `code`.*
 FROM `source_code` `code`
 LEFT JOIN `source_code` `origin`
  ON `code`.`fork_origin_id` = `origin`.`id`

以上で、いっさいSQL文字列結合をすることなく、あらゆる関係で必要に応じてテーブルエイリアスを指定する方法がわかりました。

SQL文でなくクエリビルダにこだわるべき理由

SQL を扱うとき、たとえそれがテーブル名や構文であったとしても、文字列結合を避けたほうがいいのはたしかです。Yii のクエリビルダは、テーブル名やカラム名を含むあらゆる単語をクオートして、SQLインジェクション安全を確保しようとします。(もちろん可能な箇所ではすべての値をプレースホルダにします)

少しでも文字列で SQL 構文を表してしまうと、その恩恵にあずかることができません。また、長い文字列を書くとタイポのリスクも増えます。仮にミスしても PHP 構文エラーで引っかかるような書き方で表現できる範囲を増やすことが、Yii のデータベースを扱うコード品質の向上につながります。

6
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
5