7 フォロワー

クエリビルダー

データベースアクセスオブジェクト の上に構築されたクエリビルダーを使用すると、プログラム的かつ DBMS に依存しない方法で SQL クエリを構築できます。生の SQL ステートメントを記述する代わりに、クエリビルダーを使用すると、より可読性の高い SQL 関連コードを記述し、より安全な SQL ステートメントを生成するのに役立ちます。

クエリビルダーの使用には通常、次の 2 つのステップが含まれます。

  1. SELECT SQL ステートメントのさまざまな部分 (例: SELECT、FROM) を表す yii\db\Query オブジェクトを構築します。
  2. yii\db\Query のクエリメソッド (例: all()) を実行して、データベースからデータを取得します。

次のコードは、クエリビルダーの一般的な使用方法を示しています。

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->all();

上記のコードは、次の SQL クエリを生成して実行します。ここで、:last_name パラメーターは文字列 'Smith' にバインドされます。

SELECT `id`, `email` 
FROM `user`
WHERE `last_name` = :last_name
LIMIT 10

情報: 通常、yii\db\Query を主に使い、yii\db\QueryBuilder を直接使うことはあまりありません。後者は、クエリメソッドのいずれかを呼び出すと、前者によって暗黙的に呼び出されます。yii\db\QueryBuilder は、DBMSに依存しない yii\db\Query オブジェクトから、DBMSに依存するSQLステートメント（例えば、テーブル/カラム名を異なる方法でクォートするなど）を生成する役割を担うクラスです。

クエリの構築

yii\db\Query オブジェクトを構築するには、さまざまなクエリ構築メソッドを呼び出して、SQLクエリのさまざまな部分を指定します。これらのメソッドの名前は、対応するSQLステートメントの部分で使用されるSQLキーワードに似ています。例えば、SQLクエリの FROM 部分を指定するには、from() メソッドを呼び出します。すべてのクエリ構築メソッドは、クエリオブジェクト自体を返すため、複数の呼び出しを連結することができます。

以下では、各クエリ構築メソッドの使用法について説明します。

select()

select() メソッドは、SQLステートメントの SELECT フラグメントを指定します。選択するカラムは、以下のように配列または文字列で指定できます。選択されたカラム名は、クエリオブジェクトからSQLステートメントが生成される際に自動的にクォートされます。

$query->select(['id', 'email']);

// equivalent to:

$query->select('id, email');

選択されるカラム名には、生のSQLクエリを書くときのように、テーブルプレフィックスやカラムエイリアスを含めることができます。例えば、

$query->select(['user.id AS user_id', 'email']);

// equivalent to:

$query->select('user.id AS user_id, email');

配列形式でカラムを指定する場合、配列のキーを使ってカラムエイリアスを指定することもできます。例えば、上記のコードは以下のように書き換えることができます。

$query->select(['user_id' => 'user.id', 'email']);

クエリを構築するときに select() メソッドを呼び出さない場合、* が選択されます。これは、すべて のカラムを選択することを意味します。

カラム名に加えて、DB式も選択できます。カンマを含むDB式を選択する場合は、自動的な名前のクォートが誤って行われないように、配列形式を使用する必要があります。例えば、

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

生のSQLが関わるすべての場所と同様に、selectでDB式を書く際は、テーブル名とカラム名にDBMSに依存しないクォート構文を使用できます。

バージョン2.0.1以降では、サブクエリを選択することもできます。各サブクエリは、yii\db\Query オブジェクトとして指定する必要があります。例えば、

$subQuery = (new Query())->select('COUNT(*)')->from('user');

// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('post');

重複しない行を選択するには、以下のように distinct() を呼び出すことができます。

// SELECT DISTINCT `user_id` ...
$query->select('user_id')->distinct();

追加のカラムを選択するには、addSelect() を呼び出すことができます。例えば、

$query->select(['id', 'username'])
    ->addSelect(['email']);

from()

from() メソッドは、SQLステートメントの FROM フラグメントを指定します。例えば、

// SELECT * FROM `user`
$query->from('user');

選択元のテーブルは、文字列または配列で指定できます。テーブル名には、生のSQLステートメントを書くときのように、スキーマプレフィックスやテーブルエイリアスを含めることができます。例えば、

$query->from(['public.user u', 'public.post p']);

// equivalent to:

$query->from('public.user u, public.post p');

配列形式を使用する場合、配列のキーを使用してテーブルエイリアスを指定することもできます。例えば、

$query->from(['u' => 'public.user', 'p' => 'public.post']);

テーブル名に加えて、yii\db\Query オブジェクトとして指定することで、サブクエリから選択することもできます。例えば、

$subQuery = (new Query())->select('id')->from('user')->where('status=1');

// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u 
$query->from(['u' => $subQuery]);

プレフィックス

デフォルトの tablePrefix を適用することもできます。実装手順は、「データベースアクセスオブジェクト」ガイドの「テーブルのクォート」セクションにあります。

where()

where() メソッドは、SQLクエリの WHERE フラグメントを指定します。WHERE 条件を指定するには、次の4つの形式のいずれかを使用できます。

  • 文字列形式。例: 'status=1'
  • ハッシュ形式。例: ['status' => 1, 'type' => 2]
  • 演算子形式。例: ['like', 'name', 'test']
  • オブジェクト形式。例: new LikeCondition('name', 'LIKE', 'test')

文字列形式

文字列形式は、非常に単純な条件を指定する場合や、DBMSの組み込み関数を使用する必要がある場合に最適です。生のSQLを書くのと同様に機能します。例えば、

$query->where('status=1');

// or use parameter binding to bind dynamic parameter values
$query->where('status=:status', [':status' => $status]);

// raw SQL using MySQL YEAR() function on a date field
$query->where('YEAR(somedate) = 2015');

特に変数の値がエンドユーザーの入力から来る場合、以下のように変数を直接条件に埋め込まないでください。これは、アプリケーションをSQLインジェクション攻撃にさらすことになるためです。

// Dangerous! Do NOT do this unless you are very certain $status must be an integer.
$query->where("status=$status");

パラメータバインディング を使用する場合は、params() または addParams() を呼び出して、パラメータを個別に指定できます。

$query->where('status=:status')
    ->addParams([':status' => $status]);

生のSQLが関わるすべての場所と同様に、文字列形式で条件を書く際は、テーブル名とカラム名にDBMSに依存しないクォート構文を使用できます。

ハッシュ形式

ハッシュ形式は、それぞれが単純な等価アサーションである複数の AND で連結されたサブ条件を指定する場合に最適です。カラム名をキーとし、対応するカラムの値として対応する値を格納した配列として記述します。例えば、

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
$query->where([
    'status' => 10,
    'type' => null,
    'id' => [4, 8, 15],
]);

ご覧のとおり、クエリビルダーは、nullまたは配列である値を適切に処理するのに十分な賢さを備えています。

以下のように、ハッシュ形式でサブクエリを使用することもできます。

$userQuery = (new Query())->select('id')->from('user');

// ...WHERE `id` IN (SELECT `id` FROM `user`)
$query->where(['id' => $userQuery]);

ハッシュ形式を使用すると、Yiiは内部で値にパラメータバインディングを適用するため、文字列形式とは対照的に、ここではパラメータを手動で追加する必要はありません。ただし、Yiiはカラム名をエスケープしないため、追加のチェックなしにユーザー側から取得した変数をカラム名として渡すと、アプリケーションがSQLインジェクション攻撃に対して脆弱になることに注意してください。アプリケーションを安全に保つには、変数とカラム名を使用しないか、変数を許可リストに対してフィルタリングしてください。ユーザーからカラム名を取得する必要がある場合は、データフィルタリングガイド記事をお読みください。例えば、以下のコードは脆弱です。

// Vulnerable code:
$column = $request->get('column');
$value = $request->get('value');
$query->where([$column => $value]);
// $value is safe, but $column name won't be encoded!

演算子形式

演算子形式を使用すると、プログラムによる方法で任意の条件を指定できます。次の形式を取ります。

[operator, operand1, operand2, ...]

オペランドはそれぞれ、文字列形式、ハッシュ形式、または演算子形式で再帰的に指定でき、演算子は次のいずれかになります。

  • and: オペランドは AND を使用して連結する必要があります。例えば、['and', 'id=1', 'id=2'] は id=1 AND id=2 を生成します。オペランドが配列の場合、ここに記述されているルールを使用して文字列に変換されます。例えば、['and', 'type=1', ['or', 'id=1', 'id=2']] は type=1 AND (id=1 OR id=2) を生成します。このメソッドは、クォートやエスケープは行いません。

  • or: オペランドが OR を使用して連結される点を除いて、and 演算子と同様です。

  • not: 1つのオペランドのみが必要で、NOT() でラップされます。例えば、['not', 'id=1'] は NOT (id=1) を生成します。オペランド1は、複数の式を記述するための配列にすることもできます。例えば、['not', ['status' => 'draft', 'name' => 'example']] は NOT ((status='draft') AND (name='example')) を生成します。

  • between: オペランド1はカラム名で、オペランド2と3はカラムが存在する範囲の開始値と終了値である必要があります。例えば、['between', 'id', 1, 10] は id BETWEEN 1 AND 10 を生成します。値が2つのカラムの間（例えば、11 BETWEEN min_id AND max_id）にある条件を構築する必要がある場合は、BetweenColumnsCondition を使用する必要があります。条件のオブジェクト定義の詳細については、条件 - オブジェクト形式の章を参照してください。

  • not between: 生成された条件で BETWEEN が NOT BETWEEN に置き換えられる点を除いて、between と同様です。

  • in: オペランド1はカラムまたはDB式である必要があります。オペランド2は、配列または Query オブジェクトのいずれかになります。IN 条件が生成されます。オペランド2が配列の場合、カラムまたはDB式が持つべき値の範囲を表します。オペランド2が Query オブジェクトの場合、サブクエリが生成され、カラムまたはDB式の範囲として使用されます。例えば、['in', 'id', [1, 2, 3]] は id IN (1, 2, 3) を生成します。このメソッドは、カラム名を適切にクォートし、範囲内の値をエスケープします。in 演算子は、複合カラムもサポートしています。この場合、オペランド1はカラムの配列である必要があり、オペランド2はカラムの範囲を表す配列の配列または Query オブジェクトである必要があります。例えば、['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]] は (id, name) IN ((1, 'oy')) を生成します。

  • not in: 生成された条件で IN が NOT IN に置き換えられる点を除いて、in 演算子と同様です。

  • like: オペランド1はカラムまたはDB式で、オペランド2はカラムまたはDB式が類似する値を示す文字列または配列である必要があります。例えば、['like', 'name', 'tester'] は name LIKE '%tester%' を生成します。値の範囲が配列として指定されている場合、複数の LIKE 述語が生成され、AND を使用して連結されます。例えば、['like', 'name', ['test', 'sample']] は name LIKE '%test%' AND name LIKE '%sample%' を生成します。値の特殊文字をエスケープする方法を指定するために、オプションの3番目のオペランドを提供することもできます。オペランドは、特殊文字からエスケープされた対応文字へのマッピングの配列である必要があります。このオペランドが指定されていない場合は、デフォルトのエスケープマッピングが使用されます。値が既にエスケープされており、エスケープを適用する必要がないことを示すには、false または空の配列を使用できます。エスケープマッピングを使用する場合（または3番目のオペランドが提供されない場合）、値は自動的にパーセント文字のペアで囲まれることに注意してください。

    注意: PostgreSQLを使用する場合は、大文字と小文字を区別しないマッチングに like の代わりに ilike を使用することもできます。

  • or like: like演算子に似ていますが、オペランド2が配列の場合、LIKE述語を連結するためにORが使用される点が異なります。

  • not like: like演算子に似ていますが、生成される条件でLIKEがNOT LIKEに置き換えられる点が異なります。

  • or not like: not like演算子に似ていますが、NOT LIKE述語を連結するためにORが使用される点が異なります。

  • exists: サブクエリを表すyii\db\Queryのインスタンスである必要がある1つのオペランドが必要です。これにより、EXISTS (サブクエリ)式が構築されます。

  • not exists: exists演算子に似ており、NOT EXISTS (サブクエリ)式を構築します。

  • >, <=、または2つのオペランドを取るその他の有効なDB演算子: 最初のオペランドは列名である必要があり、2番目のオペランドは値である必要があります。たとえば、['>', 'age', 10]はage>10を生成します。

演算子形式を使用する場合、Yiiは内部で値のパラメータバインディングを使用するため、文字列形式とは対照的に、ここでパラメータを手動で追加する必要はありません。ただし、Yiiは列名をエスケープしないため、列名として変数を渡すと、アプリケーションはSQLインジェクション攻撃に対して脆弱になる可能性が高いことに注意してください。アプリケーションを安全に保つためには、列名として変数を使用しないか、許可リストに対して変数をフィルタリングします。ユーザーから列名を取得する必要がある場合は、データフィルタリングのガイド記事を参照してください。たとえば、次のコードは脆弱です。

// Vulnerable code:
$column = $request->get('column');
$value = $request->get('value');
$query->where(['=', $column, $value]);
// $value is safe, but $column name won't be encoded!

オブジェクト形式

オブジェクト形式は2.0.14以降で使用可能であり、最も強力で最も複雑な条件を定義する方法です。クエリビルダー上に独自の抽象化を構築する場合、または独自の複雑な条件を実装する場合は、この形式に従う必要があります。

条件クラスのインスタンスはイミュータブルです。それらの唯一の目的は、条件データを格納し、条件ビルダーのゲッターを提供することです。条件ビルダーは、条件に格納されたデータをSQL式に変換するロジックを保持するクラスです。

内部的には、上記で説明した形式は、生のSQLを構築する前に暗黙的にオブジェクト形式に変換されるため、単一の条件で形式を組み合わせることができます。

$query->andWhere(new OrCondition([
    new InCondition('type', 'in', $types),
    ['like', 'name', '%good%'],
    'disabled=false'
]))

演算子形式からオブジェクト形式への変換は、QueryBuilder::conditionClassesプロパティに従って実行されます。このプロパティは、演算子名を代表的なクラス名にマッピングします。

  • AND, OR -> yii\db\conditions\ConjunctionCondition
  • NOT -> yii\db\conditions\NotCondition
  • IN, NOT IN -> yii\db\conditions\InCondition
  • BETWEEN, NOT BETWEEN -> yii\db\conditions\BetweenCondition

など。

オブジェクト形式を使用すると、独自の条件を作成したり、デフォルトの条件の構築方法を変更したりできます。詳細については、カスタム条件と式の追加の章を参照してください。

条件の追加

andWhere()またはorWhere()を使用して、既存の条件に追加の条件を追加できます。これらのメソッドを複数回呼び出して、複数の条件を個別に追加できます。たとえば、

$status = 10;
$search = 'yii';

$query->where(['status' => $status]);

if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}

$searchが空でない場合、次のWHERE条件が生成されます

WHERE (`status` = 10) AND (`title` LIKE '%yii%')

条件のフィルタリング

エンドユーザーからの入力に基づいてWHERE条件を構築する場合、通常、空の入力値は無視したいと考えます。たとえば、ユーザー名とメールで検索できる検索フォームでは、ユーザーがユーザー名/メール入力フィールドに何も入力しなかった場合、ユーザー名/メールの条件を無視したいでしょう。この目標は、filterWhere()メソッドを使用することで実現できます。

// $username and $email are from user inputs
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

filterWhere()とwhere()の唯一の違いは、前者がハッシュ形式の条件で指定された空の値を無視することです。したがって、$emailが空で、$usernameが空でない場合、上記のコードはSQL条件WHERE username=:usernameになります。

情報: 値は、null、空の配列、空の文字列、または空白のみで構成される文字列の場合、空と見なされます。

andWhere()やorWhere()と同様に、andFilterWhere()とorFilterWhere()を使用して、既存の条件に追加のフィルター条件を追加できます。

さらに、値の内容に基づいて演算子をインテリジェントに決定できるyii\db\Query::andFilterCompare()があります

$query->andFilterCompare('name', 'John Doe');
$query->andFilterCompare('rating', '>9');
$query->andFilterCompare('value', '<=100');

演算子を明示的に指定することもできます

$query->andFilterCompare('name', 'Doe', 'like');

Yii 2.0.11以降、HAVING条件にも同様のメソッドがあります。

orderBy()

orderBy()メソッドは、SQLクエリのORDER BYフラグメントを指定します。たとえば、

// ... ORDER BY `id` ASC, `name` DESC
$query->orderBy([
    'id' => SORT_ASC,
    'name' => SORT_DESC,
]);

上記のコードでは、配列のキーは列名であり、配列の値は対応する並べ替え方向です。PHP定数SORT_ASCは昇順ソートを指定し、SORT_DESCは降順ソートを指定します。

ORDER BYに単純な列名のみが含まれる場合は、生のSQLステートメントを記述する場合と同様に、文字列を使用して指定できます。たとえば、

$query->orderBy('id ASC, name DESC');

注: ORDER BYにDB式が含まれる場合は、配列形式を使用する必要があります。

addOrderBy()を呼び出して、ORDER BYフラグメントに追加の列を追加できます。たとえば、

$query->orderBy('id ASC')
    ->addOrderBy('name DESC');

groupBy()

groupBy()メソッドは、SQLクエリのGROUP BYフラグメントを指定します。たとえば、

// ... GROUP BY `id`, `status`
$query->groupBy(['id', 'status']);

GROUP BYに単純な列名のみが含まれる場合は、生のSQLステートメントを記述する場合と同様に、文字列を使用して指定できます。たとえば、

$query->groupBy('id, status');

注: GROUP BYにDB式が含まれる場合は、配列形式を使用する必要があります。

addGroupBy()を呼び出して、GROUP BYフラグメントに追加の列を追加できます。たとえば、

$query->groupBy(['id', 'status'])
    ->addGroupBy('age');

having()

having()メソッドは、SQLクエリのHAVINGフラグメントを指定します。これは、where()の場合と同じ方法で指定できる条件を取ります。たとえば、

// ... HAVING `status` = 1
$query->having(['status' => 1]);

条件を指定する方法の詳細については、where()のドキュメントを参照してください。

andHaving()またはorHaving()を呼び出して、HAVINGフラグメントに追加の条件を追加できます。たとえば、

// ... HAVING (`status` = 1) AND (`age` > 30)
$query->having(['status' => 1])
    ->andHaving(['>', 'age', 30]);

limit()とoffset()

limit()メソッドとoffset()メソッドは、SQLクエリのLIMITフラグメントとOFFSETフラグメントを指定します。たとえば、

// ... LIMIT 10 OFFSET 20
$query->limit(10)->offset(20);

無効な制限またはオフセット(例: 負の値)を指定すると、無視されます。

情報: LIMITとOFFSETをサポートしないDBMS(例: MSSQL)の場合、クエリビルダーはLIMIT/OFFSETの動作をエミュレートするSQLステートメントを生成します。

join()

join()メソッドは、SQLクエリのJOINフラグメントを指定します。たとえば、

// ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id`
$query->join('LEFT JOIN', 'post', 'post.user_id = user.id');

join()メソッドは4つのパラメータを取ります

  • $type: joinタイプ。たとえば、'INNER JOIN'、'LEFT JOIN'など。
  • $table: 結合するテーブルの名前。
  • $on: オプション。結合条件、つまり、ONフラグメント。条件の指定に関する詳細については、where()を参照してください。配列構文は列ベースの条件を指定するために機能しないことに注意してください。たとえば、['user.id' => 'comment.userId']は、ユーザーIDが文字列'comment.userId'と等しい必要がある条件になります。代わりに文字列構文を使用し、条件を'user.id = comment.userId'として指定する必要があります。
  • $params: オプション。結合条件にバインドされるパラメータ。

次のショートカットメソッドを使用して、それぞれINNER JOIN、LEFT JOIN、RIGHT JOINを指定できます。

たとえば、

$query->leftJoin('post', 'post.user_id = user.id');

複数のテーブルと結合するには、上記の結合メソッドをテーブルごとに複数回呼び出します。

テーブルとの結合に加えて、サブクエリと結合することもできます。これを行うには、結合するサブクエリをyii\db\Queryオブジェクトとして指定します。たとえば、

$subQuery = (new \yii\db\Query())->from('post');
$query->leftJoin(['u' => $subQuery], 'u.id = author_id');

この場合、サブクエリを配列に入れ、配列キーを使用してエイリアスを指定する必要があります。

union()

union()メソッドは、SQLクエリのUNIONフラグメントを指定します。たとえば、

$query1 = (new \yii\db\Query())
    ->select("id, category_id AS type, name")
    ->from('post')
    ->limit(10);

$query2 = (new \yii\db\Query())
    ->select('id, type, name')
    ->from('user')
    ->limit(10);

$query1->union($query2);

union()を複数回呼び出して、さらにUNIONフラグメントを追加できます。

withQuery()

withQuery()メソッドは、SQLクエリのWITHプレフィックスを指定します。サブクエリの代わりに、可読性といくつかのユニークな機能(再帰CTE)のために使用できます。詳細については、modern-sqlを参照してください。たとえば、このクエリは、adminのすべてのネストされた権限を、それらの子とともに再帰的に選択します。

$initialQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from(['aic' => 'auth_item_child'])
    ->where(['parent' => 'admin']);

$recursiveQuery = (new \yii\db\Query())
    ->select(['aic.parent', 'aic.child'])
    ->from(['aic' => 'auth_item_child'])
    ->innerJoin('t1', 't1.child = aic.parent');

$mainQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from('t1')
    ->withQuery($initialQuery->union($recursiveQuery), 't1', true);

withQuery()は、メインクエリにさらにCTEを先頭に追加するために複数回呼び出すことができます。クエリは、アタッチされた順序と同じ順序で先頭に追加されます。クエリの1つが再帰的である場合、CTE全体が再帰的になります。

クエリメソッド

yii\db\Query は、さまざまなクエリ目的に対応する多くのメソッドを提供しています。

  • all()：各行が名前と値のペアの連想配列である行の配列を返します。
  • one()：結果の最初の行を返します。
  • column()：結果の最初の列を返します。
  • scalar()：結果の最初の行と最初の列にあるスカラー値を返します。
  • exists()：クエリに結果が含まれているかどうかを示す値を返します。
  • count()：COUNT クエリの結果を返します。
  • sum($q)、average($q)、max($q)、min($q) を含む他の集計クエリメソッド。これらのメソッドでは $q パラメータが必須であり、列名またはDB式を指定できます。

たとえば、

// SELECT `id`, `email` FROM `user`
$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->all();
    
// SELECT * FROM `user` WHERE `username` LIKE `%test%`
$row = (new \yii\db\Query())
    ->from('user')
    ->where(['like', 'username', 'test'])
    ->one();

注意：one() メソッドは、クエリ結果の最初の行のみを返します。生成されるSQLステートメントに LIMIT 1 を追加するわけではありません。クエリが1つまたは少数のデータ行のみを返すことがわかっている場合（例えば、主キーを使用してクエリしている場合）は、これで問題なく、推奨されます。ただし、クエリが多数のデータ行を返す可能性がある場合は、パフォーマンスを向上させるために、limit(1) を明示的に呼び出す必要があります。例えば、(new \yii\db\Query())->from('user')->limit(1)->one() のようにします。

これらのクエリメソッドはすべて、オプションの $db パラメータを受け取ります。これは、DBクエリを実行するために使用する必要があるDB接続を表します。このパラメータを省略した場合、db アプリケーションコンポーネントがDB接続として使用されます。以下は、count() クエリメソッドを使用した別の例です。

// executes SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name
$count = (new \yii\db\Query())
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->count();

yii\db\Query のクエリメソッドを呼び出すと、実際には内部で次の作業が行われます。

  • yii\db\QueryBuilder を呼び出して、yii\db\Query の現在の構造に基づいてSQLステートメントを生成します。
  • 生成されたSQLステートメントを使用して、yii\db\Command オブジェクトを作成します。
  • yii\db\Command のクエリメソッド（例えば、queryAll()）を呼び出して、SQLステートメントを実行し、データを取得します。

場合によっては、yii\db\Query オブジェクトから作成されたSQLステートメントを調べたり使用したりしたい場合があります。これは、次のコードで実現できます。

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->createCommand();
    
// show the SQL statement
echo $command->sql;
// show the parameters to be bound
print_r($command->params);

// returns all rows of the query result
$rows = $command->queryAll();

クエリ結果のインデックス付け

all() を呼び出すと、連続した整数でインデックス付けされた行の配列が返されます。特定の列または式の値でインデックスを付けるなど、異なる方法でインデックスを付けたい場合があります。これは、indexBy() を all() の前に呼び出すことで実現できます。例えば、

// returns [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]
$query = (new \yii\db\Query())
    ->from('user')
    ->limit(10)
    ->indexBy('id')
    ->all();

indexBy() メソッドに渡される名前の列は、インデックス付けが機能するためには、結果セットに存在する必要があります。これは開発者の責任です。

式の値でインデックスを付けるには、無名関数を indexBy() メソッドに渡します。

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy(function ($row) {
        return $row['id'] . $row['username'];
    })->all();

無名関数は、現在の行データを含むパラメータ $row を受け取り、現在の行のインデックス値として使用されるスカラー値を返す必要があります。

注意：SQLに変換されクエリの一部である、groupBy() や orderBy() などのクエリメソッドとは対照的に、このメソッドはデータベースからデータがフェッチされた後に機能します。つまり、クエリのSELECTに含まれている列名のみを使用できます。また、customer.id のようにテーブルプレフィックス付きの列を選択した場合、結果セットには id のみが含まれるため、テーブルプレフィックスなしで ->indexBy('id') を呼び出す必要があります。

バッチクエリ

大量のデータを扱う場合、yii\db\Query::all() などのメソッドは、クエリ結果全体をクライアントのメモリにロードする必要があるため、適切ではありません。この問題を解決するために、Yiiはバッチクエリをサポートしています。サーバーはクエリ結果を保持し、クライアントはカーソルを使用して、結果セットを一度に1バッチずつ反復処理します。

警告：バッチクエリのMySQL実装には既知の制限事項と回避策があります。下記を参照してください。

バッチクエリは次のように使用できます。

use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->orderBy('id');

foreach ($query->batch() as $users) {
    // $users is an array of 100 or fewer rows from the user table
}

// or to iterate the row one by one
foreach ($query->each() as $user) {
    // data is being fetched from the server in batches of 100,
    // but $user represents one row of data from the user table
}

yii\db\Query::batch() メソッドと yii\db\Query::each() メソッドは、Iterator インターフェイスを実装する yii\db\BatchQueryResult オブジェクトを返します。したがって、foreach コンストラクトで使用できます。最初の反復処理中に、SQLクエリがデータベースに対して行われます。その後、残りの反復処理でデータがバッチでフェッチされます。デフォルトでは、バッチサイズは100です。つまり、各バッチで100行のデータがフェッチされます。バッチサイズは、batch() または each() メソッドに最初のパラメータを渡すことで変更できます。

yii\db\Query::all() と比較して、バッチクエリは一度に100行のデータのみをメモリにロードします。

yii\db\Query::indexBy() を使用して、特定の列でインデックス付けされるようにクエリ結果を指定した場合でも、バッチクエリは適切なインデックスを保持します。

例えば、

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy('username');

foreach ($query->batch() as $users) {
    // $users is indexed by the "username" column
}

foreach ($query->each() as $username => $user) {
    // ...
}

MySQLにおけるバッチクエリの制限

MySQLのバッチクエリの実装は、PDOドライバライブラリに依存しています。デフォルトでは、MySQLクエリはbuffered です。これにより、カーソルを使用してデータを取得する目的が損なわれるため、ドライバによって結果セット全体がクライアントのメモリにロードされるのを防ぐことができません。

注意：libmysqlclient が使用されている場合（PHP5の典型的なケース）、PHPのメモリ制限は結果セットに使用されるメモリをカウントしません。バッチクエリが正しく機能しているように見えるかもしれませんが、実際にはデータセット全体がクライアントのメモリにロードされ、それを使い果たす可能性があります。

バッファリングを無効にしてクライアントのメモリ要件を減らすには、PDO接続プロパティ PDO::MYSQL_ATTR_USE_BUFFERED_QUERY を false に設定する必要があります。ただし、データセット全体が取得されるまで、同じ接続を介して他のクエリを実行することはできません。これにより、ActiveRecord が必要なときにテーブルスキーマを取得するためのクエリを実行できなくなる可能性があります。これが問題ではない場合（テーブルスキーマが既にキャッシュされている場合）、元の接続を非バッファモードに切り替え、バッチクエリが完了したらロールバックすることができます。

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// Do batch query

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

注意：MyISAMの場合、バッチクエリの間、テーブルがロックされ、他の接続の書き込みアクセスが遅延または拒否される可能性があります。非バッファクエリを使用する場合は、カーソルを可能な限り短い時間だけ開いたままにしてください。

スキーマがキャッシュされていない場合、またはバッチクエリの処理中に他のクエリを実行する必要がある場合は、データベースへの個別の非バッファ接続を作成できます。

$unbufferedDb = new \yii\db\Connection([
    'dsn' => Yii::$app->db->dsn,
    'username' => Yii::$app->db->username,
    'password' => Yii::$app->db->password,
    'charset' => Yii::$app->db->charset,
]);
$unbufferedDb->open();
$unbufferedDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$unbufferedDb が元のバッファリングされた $db とまったく同じPDO属性を持ち、PDO::MYSQL_ATTR_USE_BUFFERED_QUERY が false になっていることを確認したい場合は、$db のディープコピーを検討し、手動でfalseに設定します。

次に、クエリは通常どおり作成されます。新しい接続は、バッチクエリを実行し、結果をバッチで、または1つずつ取得するために使用されます。

// getting data in batches of 1000
foreach ($query->batch(1000, $unbufferedDb) as $users) {
    // ...
}


// data is fetched from server in batches of 1000, but is iterated one by one 
foreach ($query->each(1000, $unbufferedDb) as $user) {
    // ...
}

接続が不要になり、結果セットが取得されたら、閉じることができます。

$unbufferedDb->close();

注意：非バッファクエリはPHP側で使用するメモリが少なくなりますが、MySQLサーバーの負荷が増加する可能性があります。大量のデータに対する本番環境でのプラクティスに合わせてコードを設計することを推奨します。たとえば、整数キーの範囲を分割し、非バッファクエリでループします。

カスタム条件と式の追加

条件 – オブジェクト形式 の章で述べたように、カスタムの条件クラスを作成できます。例えば、特定の列が特定の値よりも小さいかどうかをチェックする条件を作成しましょう。演算子形式を使用すると、次のようになります。

[
    'and',
    ['>', 'posts', $minLimit],
    ['>', 'comments', $minLimit],
    ['>', 'reactions', $minLimit],
    ['>', 'subscriptions', $minLimit]
]

このような条件が1回適用される場合は問題ありません。単一のクエリで複数回使用される場合は、大幅に最適化できます。これを実証するために、カスタムの条件オブジェクトを作成しましょう。

Yiiには、条件を表すクラスをマークするために使用する必要がある ConditionInterface があります。配列形式から条件を作成できるようにするために、fromArrayDefinition() メソッドの実装が必要です。不要な場合は、例外をスローしてこのメソッドを実装できます。

カスタムの条件クラスを作成するので、タスクに最適なAPIを構築できます。

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    private $columns;
    private $value;

    /**
     * @param string[] $columns Array of columns that must be greater, than $value
     * @param mixed $value the value to compare each $column against.
     */
    public function __construct(array $columns, $value)
    {
        $this->columns = $columns;
        $this->value = $value;
    }
    
    public static function fromArrayDefinition($operator, $operands)
    {
        throw new InvalidArgumentException('Not implemented yet, but we will do it later');
    }
    
    public function getColumns() { return $this->columns; }
    public function getValue() { return $this->vaule; }
}

したがって、条件オブジェクトを作成できます。

$condition = new AllGreaterCondition(['col1', 'col2'], 42);

しかし、QueryBuilder は、このオブジェクトからSQL条件を作成する方法をまだ知りません。次に、この条件のビルダーを作成する必要があります。build() メソッドを実装するために、yii\db\ExpressionBuilderInterface を実装する必要があります。

namespace app\db\conditions;

class AllGreaterConditionBuilder implements \yii\db\ExpressionBuilderInterface
{
    use \yii\db\ExpressionBuilderTrait; // Contains constructor and `queryBuilder` property.

    /**
     * @param ExpressionInterface $condition the condition to be built
     * @param array $params the binding parameters.
     * @return AllGreaterCondition
     */ 
    public function build(ExpressionInterface $expression, array &$params = [])
    {
        $value = $condition->getValue();
        
        $conditions = [];
        foreach ($expression->getColumns() as $column) {
            $conditions[] = new SimpleCondition($column, '>', $value);
        }

        return $this->queryBuilder->buildCondition(new AndCondition($conditions), $params);
    }
}

次に、QueryBuilder に新しい条件を知らせるため、expressionBuilders 配列にマッピングを追加します。これは、アプリケーション構成から直接行うことができます。

'db' => [
    'class' => 'yii\db\mysql\Connection',
    // ...
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
    ],
],

これで、where() で条件を使用できます。

$query->andWhere(new AllGreaterCondition(['posts', 'comments', 'reactions', 'subscriptions'], $minValue));

演算子形式を使用してカスタム条件を作成できるようにするには、QueryBuilder::conditionClasses で宣言する必要があります。

'db' => [
    'class' => 'yii\db\mysql\Connection',
    // ...
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
        'conditionClasses' => [
            'ALL>' => 'app\db\conditions\AllGreaterCondition',
        ],
    ],
],

また、app\db\conditions\AllGreaterCondition で AllGreaterCondition::fromArrayDefinition() メソッドの実際の実装を作成します。

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    // ... see the implementation above
     
    public static function fromArrayDefinition($operator, $operands)
    {
        return new static($operands[0], $operands[1]);
    }
}

その後、より短い演算子形式を使用してカスタム条件を作成できます。

$query->andWhere(['ALL>', ['posts', 'comments', 'reactions', 'subscriptions'], $minValue]);

式と条件の2つの概念が使用されていることに気付いたかもしれません。yii\db\ExpressionInterface は、yii\db\ExpressionBuilderInterface を実装する式ビルダー クラスを必要とするオブジェクトをマークするために使用する必要があります。また、yii\db\condition\ConditionInterface があり、ExpressionInterface を拡張し、上記で説明したように配列定義から作成できるオブジェクトで使用する必要がありますが、ビルダーも必要とします。

要約すると

  • 式 – は、何らかの形でSQLステートメント（演算子、文字列、配列、JSONなど）にコンパイルできるデータセットのデータ転送オブジェクト (DTO) です。
  • 条件 – は、複数の式（またはスカラー値）を集約し、単一のSQL条件にコンパイルできる式のスーパーセットです。

ExpressionInterface を実装する独自のクラスを作成して、データをSQLステートメントに変換する複雑さを隠すことができます。式のその他の例については、次の記事で詳しく説明します。

データベースアクセスオブジェクト
トップへ移動
アクティブレコード

タイプミスを見つけたり、このページを改善する必要があると思われる場合は？
githubで編集 !