Database APIを自分用にまとめてみた ~Dynamic Queries その１~

公開日：

はじめに

こちらの記事の第３弾です。

今回はDynamic Queries（動的クエリ）の項目をまとめていきます。

Dynamic Queriesには複数の機能があり、全てをこの記事内でまとめてしまうと大変長くなってしまうため、２〜３記事くらいに分けようと思います。
この記事では、

  • 概要
  • Expressions
  • Fields
  • Joins

までをまとめます。

※ 英語で書かれているドキュメントを日本語に訳しながらまとめています。よって、翻訳ミスやニュアンスの違い等があるかもしれないことをご承知おきください。

Dynamic Queriesとは

概要

動的クエリとは、明示的なクエリ文字列として提供されるのではなく、Drupalによって動的に構築されるクエリを指します。

すべてのInsert、Update、Delete、Mergeクエリは動的でなければなりません。
Selectクエリは、静的でも実行可能です。

Selectを静的クエリで実行するメリット Selectクエリは、静的クエリの方が高パフォーマンスで実行できます。

現在公開されているDrupalのサービスでは、Selectクエリは使用例の90%が静的クエリのようです。（公式ドキュメントより）

動的に構築されるクエリはすべて、適切なデータベース接続オブジェクトから要求されるクエリオブジェクトを使用して構築されます。

静的クエリと同様に、ほとんどの場合、手続きラッパーを使用してオブジェクトを要求することができます。

しかし、クエリに対する後続の指示は、クエリオブジェクトに対して呼び出されるメソッドの形をとります。

動的なSelectクエリの実行例

<?php

$database = \Drupal::database();
$query = $database->select('mytable', 'mt', $options);
$query->fields('mytable', ['field_1', 'field_2']);

上記の場合、mytableはクエリベースのテーブルで、FROM文から使用されるテーブルとなります。

静的クエリではテーブル名を括弧で囲う必要がありましたが、動的クエリではクエリビルダが自動で処理を行うため必要ありません。
また、エイリアスの指定は強制ではありません。

$database->select()が返すのは、Selectクラスのインスタンスです。

動的クエリを実行するにはフィールド値が必要なため、すべての動的クエリでaddField()やfields()の実行が必要になります。
これらを省略すると、クエリは構文的に無効となり、失敗してしまいます。

$optionsは、静的クエリでも出てきた配列$optionsと同様です。

Expressionsとは

概要

Selectクエリビルダは、フィールドリストでの式の使用をサポートしています。
例えば、2倍の年齢フィールド、すべての名前フィールドのカウント、タイトルフィールドの部分文字列などがあります。

上記のような式を追加したい場合は、addExpression()メソッドを使います。

使用するメソッド

addExpression()メソッドを使用します。

[メソッドの引数]

引数名 概要
$expressions 追加したいクエリ。
$alias フィールドのエイリアス。エイリアスが既に存在する場合は、新しいエイリアスが生成され、addExpression()の返り値がそのエイリアスとなる。
$options 式の一部として使用するプレースホルダー値の連想配列。

実行例

以下は、addExpression()メソッドを使ってCOUNT関数を実行している例です。
テーブルには2件のレコードが登録されます。

<?php

public function uidCount(int $uid): void
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('uid', $uid);
    $query->addExpression('COUNT(*)', 'count');

    $result = $query->execute()->fetchField();
    echo sprintf('result: %s', $result);
}

$query = Drupal::database()->insert('articles');

return (int) $query->fields([
    'uid',
    'title',
    'body',
])->values([
    1,
    'タイトル1',
    '本文本文本文本文本文本文本文本文'
])->values([
    1,
    'タイトル2',
    '本文本文本文本文本文本文本文本文'
])->execute();

uidCount(1);

実行すると以下のような結果が得られます。

result: 2

先述の通りレコードは2件あるため、この実行結果は正しいですね。
無事、COUNT関数が実行できているようです。

注意点

SQL式の中には、GROUP BY句を追加しないと機能しないものもあります。
この場合は、groupBy()メソッドを使用しましょう。

Fieldsとは

概要

Selectクエリにfieldを追加したり、１つのfieldのみを返すコードを書きたい場合に使用します。

fieldを追加する場合はaddField()メソッドを、複数追加する場合はfields()メソッドを使用できます。
また、実行結果として１つのfieldのみを返したい場合は、fetchField()メソッドを使用できます。

使用するメソッド

  1. addField()メソッド

fieldを１つ追加します。

引数名 概要
$table_alias テーブルのエイリアス。
$field フィールド名。
$alias フィールドのエイリアス。
  1. fields()メソッド

複数のfieldを追加します。

引数名 概要
$table_alias テーブルのエイリアス。
$fields フィールド名の配列。
  1. fetchField()メソッド

クエリの実行結果で１つのfieldのみを返します。

引数名 概要
$index 返されるフィールドの数値インデックス。デフォルトは最初のフィールドになる。

実行例

  1. addField()メソッド

以下は、addField()メソッドを使ってSelectクエリにfieldを追加している例です。

追加しているのはarticlesテーブルのidとtitleです。

<?php

public function find(int $articleId): array
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('id', $articleId);
    $query->addField('a', 'id');
    $query->addField('a', 'title');

    $result = $query->execute()->fetchAssoc();
}

Drupal::database()->insert('articles')
		->fields([
		    'uid' => 1,
		    'title' => 'タイトル1',
		    'body' => '本文本文本文本文本文本文本文本文',
		])->execute();

$article = find(1);

echo 'key: id, value: '.$article['id']."\n";
echo 'key: title, value: '.$article['title']."\n";

実行すると以下のような結果が得られます。

key: id, value: 1
key: title, value: タイトル1

idとtitleの値が取得できていますね。

  1. fields()メソッド

以下は、fields()メソッドを使ってSelectクエリにfieldを追加している例です。

articlesテーブルのすべてのfieldを追加しています。

<?php

public function find(int $articleId): array
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('id', $articleId)->fields('a');

    return $query->execute()->fetchAssoc();
}

Drupal::database()->insert('articles')
		->fields([
		    'uid' => 1,
		    'title' => 'タイトル1',
		    'body' => '本文本文本文本文本文本文本文本文',
		])->execute();

$article = find(1);

echo 'key: id, value: '.$article['id']."\n";
echo 'key: uid, value: '.$article['uid']."\n";
echo 'key: title, value: '.$article['title']."\n";
echo 'key: body, value: '.$article['body']."\n";

実行すると以下のような結果が得られます。

key: id, value: 1
key: uid, value: 1
key: title, value: タイトル1
key: body, value: 本文本文本文本文本文本文本文本文

各カラムの値が取得できていますね。

  1. fetchField()メソッド

以下は、fetchField()メソッドを使ってSelectクエリにfieldを追加している例です。

addField()メソッドでtitleを追加し、それのみを返すクエリになっています。

<?php

public function find(int $articleId)
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('id', $articleId);
    $query->addField('a', 'title');

    $result = $query->execute();
    return $result->fetchField();
}

Drupal::database()->insert('articles')
		->fields([
		    'uid' => 1,
		    'title' => 'タイトル1',
		    'body' => '本文本文本文本文本文本文本文本文',
		])->execute();
		
$article = find(1);

echo $article;

実行すると以下のような結果が得られます。

タイトル1

titleの値のみが返されていますね。

Joinsとは

概要

テーブルを別のテーブルと結合するには、join(), innerJoin(), leftJoin(), addJoin()メソッドを使用します。

join()メソッドの戻り値は、割り当てられたテーブルのエイリアスとなります。つまり、文字列です。

エイリアスが指定された場合は、エイリアスが既に別のテーブルに対して使用されている場合を除き、そのエイリアスが使用されることになります。
指定しなかったり、既に別のテーブルで使用されていた場合は、システムが自動で別のエイリアスを割り当ててくれるようになっています。

使用するメソッド

  1. join() , innerJoin() , leftJoin()メソッド

JOIN句、INNER JOIN句、LEFTJOIN句、をクエリに追加するためのメソッド。

引数名 概要
$table テーブル名。
$alias テーブル名のエイリアス。
$condition 検索条件のクエリ。リテラルや変数を、直接入れてはいけない。
$arguments $conditionに置き換える引数の配列。
  1. addJoin()メソッド

JOIN句をクエリに追加するためのメソッド。
第一引数で指定したJOINのタイプに合わせて、追加するJOIN句の種類を変える。

引数名 概要
$type joinのタイプ。INNER、LEFT OUTER、RIGHT OUTERのいずれかになる。
$table テーブル名。
$alias テーブル名のエイリアス。
$condition 検索条件のクエリ。リテラルや変数を、直接入れてはいけない。
$arguments $conditionに置き換える引数の配列。

実行例

  1. join()メソッド

以下は、join()メソッドを使ってクエリにJOIN句を追加している例です。
結合しているのは、usersテーブルです。

<?php

public function findWithUser(int $articleId): array|bool
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('a.id', $articleId);

    $query->join('users', 'u', 'u.uid = a.uid');

    $query->fields('a')->fields('u');
    return $query->execute()->fetchAssoc();
}

$user = User::create();
$user->set('name', 'テストユーザー');
$user->set('mail', 'test@example.com');
$user->set('pass', 'test');
$user->save();

Drupal::database()->insert('articles')->fields([
    'uid',
    'title',
    'body',
])->values([
    (int) $user->id(),
    'タイトル1',
    '本文本文本文本文本文本文本文本文1'
])->execute();

$article = findWithUser(1);

echo "\n";
echo 'article id: ' . $article['id'];
echo "\n";
echo 'user id: ' . $article['uid'];
echo "\n";

実行すると以下のような結果が得られます。

article id: 1
user id: 1

usersテーブルのカラムであるuidが取得できていますね。

  1. innerJoin()メソッド

以下は、innerJoin()メソッドを使ってクエリにINNER JOIN句を追加している例です。
結合しているのは、同じくusersテーブルです。

<?php

public function findWithUser(int $articleId): array|bool
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('a.id', $articleId);

    $query->innerJoin('users', 'u', 'u.uid = a.uid');

    $query->fields('a')->fields('u');
    return $query->execute()->fetchAssoc();
}

$user = User::create();
$user->set('name', 'テストユーザー');
$user->set('mail', 'test@example.com');
$user->set('pass', 'test');
$user->save();

Drupal::database()->insert('articles')->fields([
    'uid',
    'title',
    'body',
])->values([
    (int) $user->id(),
    'タイトル1',
    '本文本文本文本文本文本文本文本文1'
])->execute();

$article = findWithUser(1);

echo "\n";
echo 'article id: ' . $article['id'];
echo "\n";
echo 'user id: ' . $article['uid'];
echo "\n";

実行すると以下のような結果が得られます。

article id: 1
user id: 1

usersテーブルのカラムであるuidが取得できていますね。

  1. leftJoin()メソッド

以下は、leftJoin()メソッドを使ってクエリにLEFT JOIN句を追加している例です。
結合しているのは、同じくusersテーブルです。

<?php

public function findWithUser(int $articleId): array|bool
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('a.id', $articleId);

    $query->leftJoin('users', 'u', 'u.uid = a.uid');

    $query->fields('a')->fields('u');
    return $query->execute()->fetchAssoc();
}

$user = User::create();
$user->set('name', 'テストユーザー');
$user->set('mail', 'test@example.com');
$user->set('pass', 'test');
$user->save();

Drupal::database()->insert('articles')->fields([
    'uid',
    'title',
    'body',
])->values([
    (int) $user->id(),
    'タイトル1',
    '本文本文本文本文本文本文本文本文1'
])->execute();

$article = findWithUser(1);

echo "\n";
echo 'article id: ' . $article['id'];
echo "\n";
echo 'user id: ' . $article['uid'];
echo "\n";

実行すると以下のような結果が得られます。

article id: 1
user id: 1

usersテーブルのカラムであるuidが取得できていますね。

  1. addJoin()メソッド

以下は、addJoin()メソッドを使ってクエリにJOIN句を追加している例です。
JOIN句のタイプはINNER JOINで、結合しているのはusersテーブルです。

<?php

public function findWithUser(int $articleId): array|bool
{
    $query = Drupal::database()->select('articles', 'a')
        ->condition('a.id', $articleId);

    $query->addJoin('INNER', 'users', 'u', 'u.uid = a.uid');

    $query->fields('a')->fields('u');
    return $query->execute()->fetchAssoc();
}

$user = User::create();
$user->set('name', 'テストユーザー');
$user->set('mail', 'test@example.com');
$user->set('pass', 'test');
$user->save();

Drupal::database()->insert('articles')->fields([
    'uid',
    'title',
    'body',
])->values([
    (int) $user->id(),
    'タイトル1',
    '本文本文本文本文本文本文本文本文1'
])->execute();

$article = findWithUser(1);

echo "\n";
echo 'article id: ' . $article['id'];
echo "\n";
echo 'user id: ' . $article['uid'];
echo "\n";

実行すると以下のような結果が得られます。

article id: 1
user id: 1

usersテーブルのカラムであるuidが取得できていますね。

注意点

引数$conditionには、リテラルや変数を直接代入してはいけません。
理由は、SQLインジェクション等の攻撃を防ぐためです。

基本はクエリを直接書き込んで、可変な値を条件分に使いたい場合は引数$argumentsを使って変数を代入してください。

また、テーブル名にuserのようなリテラルを指定する代わりに、すべての結合メソッドは最初の引数としてSelectクエリを受け付けるようになっています。

そして、join()メソッドは連結できません。
複数のテーブルを結合させたい場合は、複数行に分けてjoin()メソッドを使ってください。

最後に

今回は、概要、Expressions、Fields、Joinsまでをまとめました。

次回はこの続きからまとめようと思います。

Romy(ろみぃ)

歌とゲームと本が好きな人間。ドラゴンになりたい。
不定期で記事を更新していきます。
今後、ブログ以外にもコンテンツ追加していく予定。

© Romy 2024