Doctrine入門、DQLとクエリ

Doctrine用のクエリ言語、DQL(Doctrine Query Language)での取得系を使っていきます。
SQLに似てますがSQLがテーブル相手なのに対してDQLはエンティティ相手です。
最終的に対応するデータベースのSQLに変換され実行されます。

Query

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i FROM App\Entities\Item i'
);

echo sprintf("DQL: %s\n", $query->getDQL());
echo sprintf("SQL: %s\n", $query->getSQL());

/** @var array<Item> $items */
$items = $query->getResult();

foreach($items as $item)
{
    echo "{$item->getId()} - {$item->getName()} :  {$item->getPower()}\n";
}

DQL: SELECT i FROM App\Entities\Item i
SQL: SELECT i0_.id AS id_0, i0_.name AS name_1, i0_.power AS power_2 FROM Item i0_
1 - F :  8
2 - A :  3
3 - C :  6
4 - D :  5
5 - G :  7
6 - E :  2
7 - B :  4

$manager->createQuery()

EntityManagerのcreateQuery()にDQLを渡すとクエリ(Doctrine\ORM\Query)のインスタンスが取得できます。

SELECT i FROM App\Entities\Item i

テーブルではなくエンティティ名を書きます。
エンティティ名はItem::classでも取得できます。

このクエリからDQLを取得するにはgetDQL()を使います。

$query->getDQL()

SQLを取得するにはgetSQL()を使います。

$query->getSQL()

クエリからgetResult()で結果を取得出来ます。

$items = $query->getResult();

ですが、戻り値は設定やDQLの内容によって変わるので注意が必要です。

結果によってはエンティティの配列ではない

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i.id, i.name, i.power FROM App\Entities\Item i'
);

echo sprintf("DQL: %s\n", $query->getDQL());
echo sprintf("SQL: %s\n", $query->getSQL());

$items = $query->getResult();

foreach($items as $item)
{
    print_r($item);
}

DQL: SELECT i.id, i.name, i.power FROM App\Entities\Item i
SQL: SELECT i0_.id AS id_0, i0_.name AS name_1, i0_.power AS power_2 FROM Item i0_
Array
(
    [id] => 1
    [name] => F
    [power] => 8
)
Array
(
    [id] => 2
    [name] => A
    [power] => 3
)
Array
(
    [id] => 3
    [name] => C
    [power] => 6
)
Array
(
    [id] => 4
    [name] => D
    [power] => 5
)
Array
(
    [id] => 5
    [name] => G
    [power] => 7
)
Array
(
    [id] => 6
    [name] => E
    [power] => 2
)
Array
(
    [id] => 7
    [name] => B
    [power] => 4
)

さっきのDQLとちがって、オブジェクト取得ではありません。

SELECT i.id, i.name, i.power FROM App\Entities\Item i

この場合は連想配列の配列で取得されます。

名前付きパラメータと位置パラメータ

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i FROM App\Entities\Item i WHERE i.power < :p'
);

$query->setParameter('p', 5);

echo sprintf("DQL: %s\n", $query->getDQL());
echo sprintf("SQL: %s\n", $query->getSQL());

$items = $query->getResult();

foreach($items as $item)
{
    echo "{$item->getId()} - {$item->getName()} :  {$item->getPower()}\n";
}

DQL: SELECT i FROM App\Entities\Item i WHERE i.power < :p
SQL: SELECT i0_.id AS id_0, i0_.name AS name_1, i0_.power AS power_2 FROM Item i0_ WHERE i0_.power < ?
2 - A :  3
6 - E :  2
7 - B :  4

次のDQLでは:pのように名前を付けておきます。

SELECT i FROM App\Entities\Item i WHERE i.power < :p

次にsetParameter()を使ってpの値(5)を設定します。

$query->setParameter('p', 5);

実行するとpowerが5未満のエンティティ一覧を取得出来ます。

また、

パラメータが複数ある場合は、setParameters()も使えます。

$query = $manager->createQuery(
    'SELECT i FROM App\Entities\Item i WHERE i.power >= :p1 AND i.power < :p2'
);

/*
$query->setParameter('p1', 3);
$query->setParameter('p2', 5);
*/

$query->setParameters([
    'p1' => 3,
    'p2' => 5
]);

さらに位置パラメータはクエスチョンマークを使います。

$query = $manager->createQuery(
    'SELECT i FROM App\Entities\Item i WHERE i.power >= ?1 AND i.power < ?2'
);
$query->setParameter(1, 3);
$query->setParameter(2, 5);

範囲の指定

SQLのレコード範囲を指定するにはsetFirstResult()とsetMaxResults()を使います。

MySQLやPostgreSQLではクエリの結果が複数行ある場合範囲を指定して取得する機能があります。
LIMIT句で、例えば0から数えて10番目から10個取得したりできるのです。

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i FROM App\Entities\Item i'
);

$query->setFirstResult(2); // $query->getFirstResult() === 2
$query->setMaxResults(3); // $query->getMaxResults() === 3

echo sprintf("DQL: %s\n", $query->getDQL());
echo sprintf("SQL: %s\n", $query->getSQL());

$items = $query->getResult();

foreach($items as $item)
{
    echo "{$item->getId()} - {$item->getName()} :  {$item->getPower()}\n";
}

以下はMySQLで実行した結果になります。
SQLにLIMIT/OFFSETが反映されているようです。

DQL: SELECT i FROM App\Entities\Item i
SQL: SELECT i0_.id AS id_0, i0_.name AS name_1, i0_.power AS power_2 FROM Item i0_ LIMIT 3 OFFSET 2
3 - C :  6
4 - D :  5
5 - G :  7

ただこの機能(LIMIT句)はデータベース製品によって大きく変わる部分で、
ちょっとしか使ったことありませんが、Oracleにはなかったり、SQL Serverもいまいちだったり。

実際Doctrineがどうやって実現しているのかわからないですが、SQL Serverで実行した結果もみてみましょう。

DQL: SELECT i FROM App\Entities\Item i
SQL: SELECT i0_.id AS id_0, i0_.name AS name_1, i0_.power AS power_2 FROM Item i0_ ORDER BY (SELECT 0) OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY
3 - C :  6
4 - D :  5
5 - G :  7

Oracleは手元にないので分からないですorz

ハイドレーションモード

これまで結果の取得はgetResult()で取得してきました。
取得結果の形式はハイドレーションモードで指定でき、getResult()の引数で渡すことも出来ます。

デフォルトではAbstractQuery::HYDRATE_OBJECTが設定されています。

調べると、HYDRATE_OBJECT, HYDRATE_ARRAY, HYDRATE_SCALAR, HYDRATE_SIMPLEOBJECT, HYDRATE_SCALAR_COLUMN, HYDRATE_SINGLE_SCALARがあります。
これらがどのような形式で取得できるのかを見ていきます。

use Doctrine\ORM\AbstractQuery;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i FROM App\Entities\Item i'
);
$query->setMaxResults(1);

print_r($query->getResult(AbstractQuery::HYDRATE_SCALAR));
print_r($query->getResult(AbstractQuery::HYDRATE_ARRAY));
print_r($query->getResult(AbstractQuery::HYDRATE_OBJECT));
print_r($query->getResult(AbstractQuery::HYDRATE_SIMPLEOBJECT));

//$r = $q2->getResult(AbstractQuery::HYDRATE_SINGLE_SCALAR);
//$r = $q2->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);

Array
(
    [0] => Array
        (
            [i_id] => 1
            [i_name] => F
            [i_power] => 8
        )

)
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => F
            [power] => 8
        )

)
Array
(
    [0] => App\Entities\Item Object
        (
            [id:App\Entities\Item:private] => 1
            [name:App\Entities\Item:private] => F
            [power:App\Entities\Item:private] => 8
        )

)
Array
(
    [0] => App\Entities\Item Object
        (
            [id:App\Entities\Item:private] => 1
            [name:App\Entities\Item:private] => F
            [power:App\Entities\Item:private] => 8
        )

)

例外が発生するモードはコメントアウトしてます。
このタイプの結果はこのモードでは取得出来ません。

オブジェクトで返すのがHYDRATE_OBJECTとHYDRATE_SIMPLEOBJECTで、表示は同じです。
配列を返すのがHYDRATE_SCALARとHYDRATE_ARRAYですが、両者に若干の違いがあります。
今度はオブジェクトと名指しの列も一緒に取得してみます。

use Doctrine\ORM\AbstractQuery;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i, i.power, i.name FROM App\Entities\Item i'
);
$query->setMaxResults(1);

print_r($query->getResult(AbstractQuery::HYDRATE_SCALAR));
print_r($query->getResult(AbstractQuery::HYDRATE_ARRAY));
print_r($query->getResult(AbstractQuery::HYDRATE_OBJECT));
//print_r($query->getResult(AbstractQuery::HYDRATE_SIMPLEOBJECT));

//$r = $q2->getResult(AbstractQuery::HYDRATE_SINGLE_SCALAR);
//$r = $q2->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);

Array
(
    [0] => Array
        (
            [i_id] => 1
            [i_name] => F
            [i_power] => 8
            [power] => 8
            [name] => F
        )

)
Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [name] => F
                    [power] => 8
                )

            [power] => 8
            [name] => F
        )

)
Array
(
    [0] => Array
        (
            [0] => App\Entities\Item Object
                (
                    [id:App\Entities\Item:private] => 1
                    [name:App\Entities\Item:private] => F
                    [power:App\Entities\Item:private] => 8
                )

            [power] => 8
            [name] => F
        )

)

今回はHYDRATE_SIMPLEOBJECTがエラーになるのでコメントアウトします。
前回と変わったのはここです。

SELECT i, i.power, i.name FROM App\Entities\Item i

オブジェクトiと、それ以外にi.powerやi.nameが追加になりました。

よく見るとHYDRATE_SCALARはオブジェクトが展開され一つの連想配列になって、HYDRATE_ARRAYはオブジェクトが展開された以外はHYDRATE_OBJECTと同じです。
名前からさっするにHYDRATE_SIMPLEOBJECTはItemオブジェクトとしては表現出来なかったのかなと。

getSingleColumnResult()

getSingleColumnResult()はモードがAbstractQuery::HYDRATE_SCALAR_COLUMNに値するものと同じ。

use Doctrine\ORM\EntityManager;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT i.name FROM App\Entities\Item i'
);

// HYDRATE_SCALAR_COLUMN
$names = $query->getSingleColumnResult();

print_r($names);

Array
(
    [0] => F
    [1] => A
    [2] => C
    [3] => D
    [4] => G
    [5] => E
    [6] => B
)

カラム１列分取得出来ます。
もし複数列取得のクエリを投げれば例外が発生します。

getSingleScalarResult()

レコードが１行だけ取得出来るもので、結果が１列の場合はgetSingleScalarResult()が使えます。
ハイドレーションモードがAbstractQuery::HYDRATE_SINGLE_SCALARに値するもの。

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;

/** @var EntityManager $manager */
$manager = require 'createManager.php';

/** @var Query $query */
$query = $manager->createQuery(
    'SELECT MAX(i.power) FROM App\Entities\Item i'
);

$cnt = $query->getSingleScalarResult();
echo "MAX = $cnt\n";

MAX = 8

次のような場合もこのメソッドで取得出来るようです。

SELECT i.name FROM App\Entities\Item i where i.id = 3

ちなみにこのメソッドは内部的にgetSingleResult()を呼び出してますが、これは１行あるときだけその行を返しますがそれ以外の場合は例外を発生させます。