クエリビルダ

Yii のクエリビルダはオブジェクト指向で SQL 文を書く方法を提供します。 開発者は SQL 文の個々の部分をクラスのメソッドやプロパティを使って書くことが出来ます。 そうすると、クエリビルダが異なる部分を一つの正しい SQL 文に組み上げます。 そして、さらに、組み上がった SQL 文は データアクセスオブジェクト で説明されている DAO メソッドを呼んで実行することが出来ます。 下記はクエリビルダの典型的な使用方法を示すもので、SELECT SQL 文をビルドするものです。




クエリビルダを使うのに最も適しているのは、SQL 文を手続き的に組み上げる必要があるとき、または、SQL 文をアプリケーションの何らかの条件ロジックに基づいて組み上げる必要があるときです。
クエリビルダを使用する主な便益は以下のものです。



クエリビルダの使用は強制されていません。
実際のところ、クエリが単純な場合は、直接に SQL 文を書く方が簡単で高速です。





注意: クエリビルダは、SQL 文として定義された既存のクエリを修正するためには使うことが出来ません。
  例えば、下記のコードは動作しません。

  
  

  
  
換言すると、素の SQL とクエリビルダを混用してはいけない、ということです。





クエリビルダを準備する



Yii のクエリビルダは CDbCommand、すなわち、データアクセスオブジェクト で説明されているメインの DB クエリクラスとして提供されています。



クエリビルダの使用を開始するためには、下記のように、CDbCommand の新しいインスタンスを作成します。






すなわち、Yii::app()->db を使って DB 接続を取得し、次に CDbConnection::createCommand() を呼んで必要なコマンドのインスタンスを作成します。



データアクセスオブジェクト では createCommand() の呼出しに SQL 文全体を渡しますが、ここではそうせず、空のままにしておくことに注意して下さい。
これは、下記で説明するクエリビルダのメソッドを使って、SQL 文の個々の部分を組み上げていくことになるからです。



データを読み出すクエリを組み上げる



データを読み出すクエリとは SELECT SQL 文のことです。
クエリビルダは SELECT 文の個々の部分を組み上げるための一連のメソッドを提供します。
これらのメソッドは全て CDbCommand のインスタンスを返しますので、この章の最初の例で示されているように、メソッドチェーンを使って呼び出すことが出来ます。



以下に、これらのクエリビルダのメソッドの使用方法を説明します。
説明を単純にするために、基礎になっているデータベースは MySQL であると仮定します。
他の DBMS を使っている場合は、テーブル名・カラム名・値を囲む引用符号が、例で示されているものとは違うものになることがあります。



select()






select() メソッドは、クエリの SELECT の部分を指定します。
$columns パラメータが選択されるカラムを指定します。
これは、カンマで区切ったカラム名の文字列でも、カラム名の配列でも構いません。
カラム名はテーブルプレフィックス、および/あるいは、カラム名のエイリアスを含むことが出来ます。
カラムが括弧を含んでいる (すなわち、カラムが DB の式として与えられている) 場合を除いて、カラム名は自動的に引用符号で囲まれます。



以下にいくつかの例を示します。






selectDistinct()






selectDistinct() メソッドは、DISTINCT フラグを立てることを除いて、select() メソッドと同一です。
例えば、selectDistinct('id, username') は次の SQL 文を生成します。






from()






from() メソッドは、クエリの FROM の部分を指定します。
$tables パラメータが、どのテーブルからデータを読み出すかを指定します。
これは、カンマで区切ったテーブル名の文字列でも、テーブル名の配列でも構いません。
テーブル名はスキーマプレフィックス (例えば public.tbl_user)、および/あるいは、テーブル名のエイリアス (例えば tbl_user u) を含むことが出来ます。
テーブルが括弧を含んでいる (すなわち、テーブルがサブクエリあるいは DB の式として与えられている) 場合を除いて、テーブル名は自動的に引用符号で囲まれます。
以下にいくつかの例を示します。






where()






where() メソッドはクエリの WHERE の部分を指定します。
$conditions パラメータはクエリの条件を指定し、$params はクエリ全体にバインドされるパラメータを指定します。
$conditions パラメータは、文字列 (例えば id=1) であるか、または、下記の形式の配列です。






ここで operator としては、下記のどれかを指定する事が出来ます。



以下に where の使い方の例をいくつか示します。






オペレータに like を使う場合に、パターンの中で明示的にワイルドカード文字 (% や _) を指定する必要があることに注意して下さい。
さらに、パターンがユーザ入力から来る場合は、下記のコードを使って特殊文字をエスケープし、ワイルドカード文字として扱われることを防がなければなりません。






andWhere()






addWhere() メソッドは与えられた条件をクエリの WHERE の部分に AND 演算子を使って追加します。
このメソッドの動作は、条件を置き換えるのでなく追加するという点を除いて、where() とほとんど同一です。
このメソッドのパラメータに関する詳細な情報は、where() のドキュメントを参照して下さい。



orWhere()






orWhere() メソッドは与えられた条件をクエリの WHERE の部分に OR 演算子を使って追加します。
このメソッドの動作は、条件を置き換えるのでなく追加するという点を除いて、where() とほとんど同一です。
このメソッドのパラメータに関する詳細な情報は、where() のドキュメントを参照して下さい。



order()






order() メソッドは、クエリの ORDER BY の部分を指定します。
$columns パラメータがソートに使われるカラムを指定します。
これは、カンマで区切ったカラムと並び順 (ASC または DESC) の文字列、または、カラムと並び順の配列です。
カラム名はテーブルプレフィックスを含むことが出来ます。
カラムが括弧を含んでいる (すなわち、カラムが DB の式として与えられている) 場合を除いて、カラム名は自動的に引用符号で囲まれます。



以下にいくつかの例を示します。






limit() と offset()






limit() メソッドおよび offset() メソッドは、クエリの LIMIT および OFFSET の部分を指定します。
DBMS によっては、LIMIT と OFFSET の書式がサポートされていないことがあります。
その場合は、クエリビルダは SQL 文全体を書き換えて、limit や offset の機能をシミュレートします。



以下にいくつかの例を示します。






join() とその変種






join() メソッドとその変種は、INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、CROSS JOIN、または NATURAL JOIN を使って他のテーブルを結合する方法を指定します。
$tables パラメータが、どのテーブルを結合するかを指定します。
テーブル名はスキーマプレフィックス、および/あるいは、テーブル名のエイリアスを含むことが出来ます。
テーブルが括弧を含んでおり、DB の式またはサブクエリであることを示している場合を除いて、テーブル名は自動的に引用符号で囲まれます。
$condition パラメータは結合条件を指定します。
その文法は where() の場合と同じです。
そして、$param はクエリ全体にバインドされるパラメータを指定します。



他のクエリビルダのメソッドと違って、結合メソッドは、呼ばれるたびに前のものに追加されることに注意して下さい。



以下にいくつかの例を示します。






group()






group() メソッドは、クエリの GROUP BY の部分を指定します。
$columns パラメータがグループ化されるカラムを指定します。
これは、カンマで区切ったカラム名の文字列でも、カラム名の配列でも構いません。
カラム名はテーブルプレフィックスを含むことが出来ます。
カラムが括弧を含んでいる (すなわち、カラムが DB の式として与えられている) 場合を除いて、カラム名は自動的に引用符号で囲まれます。



以下にいくつかの例を示します。






having()






having() メソッドは、クエリの HAVING の部分を指定します。
使用方法は、where() と同じです。



以下にいくつかの例を示します。






union()






union() メソッドは、クエリの UNION の部分を指定します。
これは既存の SQL に UNION 演算子を使って $sql を追加するものです。
union() を複数回呼ぶと、複数の SQL が既存の SQL に追加されます。



以下に例を示します。






クエリを実行する



上記のクエリビルダのメソッドを呼んでクエリを組み上げた後、データアクセスオブジェクト で説明されている DAO メソッドを呼んでクエリを実行することが出来ます。
例えば、CDbCommand::queryRow() を呼んで結果を一行取得したり、CDbCommand::queryAll() を呼んで全ての行をまとめて取得したり出来ます。
例えば、






SQL を読み出す



クエリビルダによって組み上げられたクエリを実行する以外に、対応する SQL 文を読み出すことも出来ます。
そのためには CDbCommand::getText() を呼びます。






クエリにバインドされるべきパラメータがある場合は、CDbCommand::params プロパティによって読み出すことが出来ます。



クエリをビルドする代替の文法



時として、メソッドチェインを用いてクエリをビルドするのが最善の選択ではない場合もあります。
Yii のクエリビルダでは、単純なオブジェクトプロパティへの代入を使ってクエリをビルドすることも出来ます。
具体的に言うと、全てのクエリビルダのメソッドには、それに対応する同じ名前のプロパティがあります。
プロパティに値を代入することは、それに対応するメソッドを呼ぶことと等価です。
例えば、次の二つの文は等価です ($command は CDbCommand オブジェクトを表すものとします)。






さらに加えて、CDbConnection::createCommand() メソッドはパラメータとして配列を取ることが出来ます。
配列内の "名前-値" のペアは、作成される CDbCommand インスタンスのプロパティを初期化するのに使われます。
すなわち、クエリをビルドするのに下記のコードを使うことが出来るという事です。






複数のクエリをビルドする



CDbCommand のインスタンスを複数回再利用して、複数のクエリをビルドすることが出来ます。
ただし、新しいクエリをビルドする前に、CDbCommand::reset() メソッドを呼び出して、前のクエリをクリーンアップしなければなりません。
例えば






データを操作するクエリをビルドする



データを操作するクエリとは、DB テーブルの中のデータを挿入、更新、および、削除する SQL 文を指すものです。
これらのクエリに対応するものとして、クエリビルダはそれぞれ insert、update、delete というメソッドを提供しています。
上で説明した SELECT クエリのメソッドと違って、これらデータ操作クエリのメソッドは、どれも、完全な SQL 文をビルドして、それをただちに実行します。



以下で、これらのデータ操作クエリのメソッドについて説明します。



insert()






insert() メソッドは、INSERT SQL 文をビルドして実行します。
$table パラメータは、どのテーブルに挿入するかを指定し、$columns は "名前-値" のペアの配列で、挿入されるカラムの値を指定するものです。
メソッドはテーブル名を引用符号で適切に囲み、挿入される値に対してパラメータバインディングを使用します。



下記がその例です。






update()






update() メソッドは、UPDATE SQL 文をビルドして実行します。
$table パラメータは、どのテーブルを更新するかを指定します。
$columns は "名前-値" のペアの配列で、更新されるカラムの値を指定します。
$conditions と $params は、where() メソッドの場合と同じで、UPDATE 文の WHERE 句を指定するものです。
メソッドはテーブル名を引用符号で適切に囲み、更新される値に対してパラメータバインディングを使用します。



下記がその例です。






delete()






delete() メソッドは、DELETE SQL 文をビルドして実行します。
$table パラメータは、どのテーブルのデータを削除するかを指定します。
$conditions と $params は、where() メソッドの場合と同じで、DELETE 文の WHERE 句を指定するものです。
メソッドはテーブル名を引用符号で適切に囲みます。



下記がその例です。






スキーマを操作するクエリをビルドする



データを読み出したり操作したりする通常のクエリの他に、クエリビルダはデータベースのスキーマを操作するための SQL クエリをビルドして実行する一連のメソッドを提供します。
具体的には、下記のクエリをサポートしています。





情報: データベーススキーマを操作する実際の SQL 文は DBMS によって大きく異なりますが、クエリビルダはこれらのクエリをビルドするための統一されたインタフェースを提供しようと試みています。
  これによって、データベースをある DBMS から別の DBMS に移行する作業が簡単になります。





抽象データ型



クエリビルダはテーブルのカラムの定義に使用出来る一連の抽象的なデータ型を導入しています。
特定の DBMS に固有で、DBMS ごとに全く異なる物理的データ型とは違って、抽象データ型は DBMS に依存しないものです。
抽象データ型がテーブルのカラムの定義に使用されると、クエリビルダはそれを対応する物理的データ型に変換します。



以下の抽象データ型がクエリビルダによってサポートされています。



createTable()






createTable() メソッドは、テーブルを作成する SQL 文をビルドして実行します。
$table パラメータは、作成されるテーブルの名前を指定します。
$columns パラメータは、新しいテーブルのカラムを指定します。
これは 名前-定義 のペア (例えば 'username'=>'string') の配列として与えなければなりません。
$options パラメータは、生成される SQL の後に追加される何らかの SQL 断片を指定するものです。
クエリビルダはテーブル名とカラム名を適切に引用符号で囲みます。



カラムの定義を指定するときに、上で述べた抽象データ型を使用することが出来ます。
クエリビルダは、現在使われている DBMS に従って、抽象データ型を対応する物理的データ型に変換します。
例えば、MySQL では、string は varchar(255) に変換されます。



カラムの定義は、抽象データ型でないデータ型や仕様を含むことも出来ます。
それらは何も変更を加えずに生成される SQL に入れられます。
例えば、point は抽象データ型ではありませんので、カラムの定義で使われた場合は、結果として得られる SQL にそのままの形で出てきます。
string NOT NULL は varchar(255) NOT NULL に変換されます (つまり、抽象データ型である string だけが変換されます)。



下記はテーブルを作成する方法を示す例です。






renameTable()






renameTable()メソッドは、テーブルの名前を変更する SQL 文をビルドして実行します。
$table パラメータは、名前を変更されるテーブルの現在の名前を指定します。
$newName パラメータは、テーブルの新しい名前を指定します。
クエリビルダはテーブル名を適切に引用符号で囲みます。



下記はテーブルの名前を変更する方法を示す例です。






dropTable()






dropTable()メソッドは、テーブルを削除する SQL 文をビルドして実行します。
$table パラメータは、削除されるテーブルの名前を指定します。
クエリビルダはテーブル名を適切に引用符号で囲みます。



下記はテーブルを削除する方法を示す例です。






truncateTable()






truncateTable()メソッドは、テーブルの行をすべて削除する SQL 文をビルドして実行します。
$table パラメータは、行をすべて削除するテーブルの名前を指定します。
クエリビルダはテーブル名を適切に引用符号で囲みます。



下記はテーブルの行をすべて削除する方法を示す例です。






addColumn()






addColumn() メソッドは、テーブルに新しいカラムを追加する SQL 文をビルドして実行します。
$table パラメータは、新しいカラムを追加されるテーブルの名前を指定します。
$column パラメータは、新しいカラムの名前を指定します。
そして $type パラメータは、新しいカラムの定義を指定するものです。
カラムの定義は、"createTable" の節で説明したように、抽象データ型を含むことが出来ます。
クエリビルダはテーブル名とカラム名を適切に引用符号で囲みます。



下記はテーブルにカラムを追加する方法を示す例です。






dropColumn()






dropColumn() メソッドは、テーブルからカラムを削除する SQL 文をビルドして実行します。
$table パラメータは、カラムを削除されるテーブルの名前を指定します。
$column パラメータは、削除されるカラムの名前を指定します。
クエリビルダはテーブル名とカラム名を適切に引用符号で囲みます。



下記はテーブルからカラムを削除する方法を示す例です。






renameColumn()






renameColumn() メソッドは、テーブルのカラム名を変更する SQL 文をビルドして実行します。
$table パラメータは、カラム名を変更されるテーブルの名前を指定します。
$name パラメータは古いカラム名、$newName パラメータは新しいカラム名を指定します。
クエリビルダはテーブル名とカラム名を適切に引用符号で囲みます。



下記はテーブルのカラム名を変更する方法を示す例です。






alterColumn()






alterColumn() メソッドは、テーブルのカラムを変更する SQL 文をビルドして実行します。
$table パラメータは、カラムを変更されるテーブルの名前を指定します。
$column パラメータは、変更されるカラムの名前を指定します。
そして $type パラメータは、カラムの新しい定義を指定するものです。
カラムの定義は、"createTable" の節で説明したように、抽象データ型を含むことが出来ます。
クエリビルダはテーブル名とカラム名を適切に引用符号で囲みます。



下記はテーブルのカラムを変更する方法を示す例です。






addForeignKey()






addForeignKey() メソッドは、テーブルに外部キー制約を追加する SQL 文をビルドして実行します。
$name パラメータは、外部キーの名前を指定します。
$table パラメータと $columns パラメータは、外部キーが関係するテーブルとカラムの名前を指定します。
複数のカラムがある場合は、カンマで区切らなければなりません。
$refTable パラメータと $refColumns パラメータは、外部キーが参照するテーブルとカラムの名前を指定します。
$delete パラメータと $update パラメータは、それぞれ、SQL 文における ON DELETE オプションと ON UPDATE オプションを指定します。
そして $type パラメータは、カラムの新しい定義を指定するものです。
カラムの定義は、"createTable" の節で説明したように、抽象データ型を含むことが出来ます。
ほとんどの DBMS は以下のオプションをサポートしています。
すなわち、RESTRICT、 CASCADE、NO ACTION、SET DEFAULT、SET NULL というオプションです。
クエリビルダはテーブル名、インデックス名、カラム名を適切に引用符号で囲みます。



下記は外部キー制約を追加する方法を示す例です。






dropForeignKey()






dropForeignKey() メソッドは、外部キー制約を削除する SQL 文をビルドして実行します。
$name パラメータは、削除する外部キー制約の名前を指定します。
$table パラメータは、その外部キー制約が課されているテーブルの名前を指定します。
クエリビルダはテーブル名と外部キー制約名を適切に引用符号で囲みます。



下記は外部キー制約を削除する方法を示す例です。






createIndex()






createIndex() メソッドは、インデックスを作成する SQL 文をビルドして実行します。
$name パラメータは、作成されるインデックスの名前を指定します。
$table パラメータは、インデックスを追加されるテーブルの名前を指定します。
$column パラメータは、インデックスされるカラムの名前を指定します。
そして $unique パラメータは、ユニークなインデックスを作成するかどうかを指定するものです。
インデックスが複数のカラムから構成される場合は、カラム名をコンマで区切らなければなりません。
クエリビルダはテーブル名、インデックス名、カラム名を適切に引用符号で囲みます。



下記はインデックスを作成する方法を示す例です。






dropIndex()






dropIndex() メソッドは、インデックスを削除する SQL 文をビルドして実行します。
$name パラメータは、削除されるインデックスの名前を指定します。
$table パラメータは、削除されるインデックスを持っているテーブルの名前を指定します。
クエリビルダはテーブル名とインデックス名を適切に引用符号で囲みます。



下記はインデックスを削除する方法を示す例です。






$Id$