Doctrine2 batch processing with ManyToMany entites

日本語訳はスクロール↓

Recently I develop a CSV export program with Doctrine2 and Symfony2. In order to deal big data results, Doctrine2 provides iterable results which is called "Batch Processing". Using the batch processing, I can output whole a rows in a table without memory leaks or memory shortage.

However, today I bumped into a problem that "when try to run a DQL which contains a JOIN of a ManyToMany entitiy, Doctrine throws an exception". Following is that exception message.

Iterate with fetch join in class Acme\BlogBundle\Entity\Post using association tags not allowed.

Above exception occured in following code:

<?php

$dql = 'SELECT posts FROM AcmeBlogBundle:Post posts INNER JOIN posts.tags tag WITH tag.name = :tag WHERE posts.blogId = :blogId';

$query = $entityManager->createQuery($dql);
$query->setParameters([
    ':blogId' => 123,
    ':tag'    => 'foo',
]);

$result = $query->iterate();

// convenience to loop
$result = new CallbackFilterIterator($result, function(&$current) use($entityManager) {
    $current = $current[0];
    $entityManager->detach($current);
    return true;
});

foreach ( $result as $post ) {
    var_dump($post->getTitle());
    var_dump($post->getTags());
}

And, this is the full exception message:

PHP Fatal error:  Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'Iterate with fetch join in class Acme\BlogBundle\Entity\Post using association tags not allowed.' in /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:136
Stack trace:
#0 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(827): Doctrine\ORM\Query\QueryException::iterateWithFetchJoinNotAllowed(Array)
#1 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(1020): Doctrine\ORM\Query\SqlWalker->walkJoinAssociationDeclaration(Object(Doctrine\ORM\Query\AST\JoinAssociationDeclaration), 3)
#2 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(756): Doctrine\ORM\Query\SqlWalker->walkJoin(Object(Doctrine\ORM\Query\AST\Join))
#3 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(463): Doctrine\ORM\Query\SqlWalker->walkFromClause(Object(Doctrine\ORM\Query\AST\FromClause))
#4 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php(38): Doctri in /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php on line 136

Through trial and error, I noticed that this exception is not thrown if I don't use the iterable results. Finally, I found out that DISTINCT is necessary to run DQL with ManyToMany entities.

So I added DISTINCT to the above PHP code, now it works well. :)

The final version DQL is like below:

$dql = 'SELECT DISTINCT posts FROM AcmeBlogBundle:Post posts INNER JOIN posts.tags tag WITH tag.name = :tag WHERE posts.blogId = :blogId';

By the way, what is Qiita?

Visit Qiita and Kobito - Great Duet to manage your knowledge. ;)

---

Doctrine2のバッチ処理でManyToManyのエンティティを使う (ja)

最近、私はDoctrine2とSymfony2でCSVエクスポートプログラムを開発しています。大きなデータ結果を取り扱うために、Doctrine2は、"Batch Processing"と呼ばれる反復可能な結果を提供します。バッチ処理を使用して、私は、メモリリークやメモリ不足せずに、テーブル内の行を全体出力することができます。

しかし、今日私は "ManyToManyのentitiyのJOINが含まれているDQLを実行しようとすると、Doctrineが例外をスローする"という問題にぶつかった。以下は、その例外のメッセージです。

Iterate with fetch join in class Acme\BlogBundle\Entity\Post using association tags not allowed.

上記の例外は、次のコードで発生しました：

<?php

$dql = 'SELECT posts FROM AcmeBlogBundle:Post posts INNER JOIN posts.tags tag WITH tag.name = :tag WHERE posts.blogId = :blogId';

$query = $entityManager->createQuery($dql);
$query->setParameters([
    ':blogId' => 123,
    ':tag'    => 'foo',
]);

$result = $query->iterate();

// convenience to loop
$result = new CallbackFilterIterator($result, function(&$current) use($entityManager) {
    $current = $current[0];
    $entityManager->detach($current);
    return true;
});

foreach ( $result as $post ) {
    var_dump($post->getTitle());
    var_dump($post->getTags());
}

そして、これは完全な例外メッセージです：

PHP Fatal error:  Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'Iterate with fetch join in class Acme\BlogBundle\Entity\Post using association tags not allowed.' in /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:136
Stack trace:
#0 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(827): Doctrine\ORM\Query\QueryException::iterateWithFetchJoinNotAllowed(Array)
#1 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(1020): Doctrine\ORM\Query\SqlWalker->walkJoinAssociationDeclaration(Object(Doctrine\ORM\Query\AST\JoinAssociationDeclaration), 3)
#2 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(756): Doctrine\ORM\Query\SqlWalker->walkJoin(Object(Doctrine\ORM\Query\AST\Join))
#3 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php(463): Doctrine\ORM\Query\SqlWalker->walkFromClause(Object(Doctrine\ORM\Query\AST\FromClause))
#4 /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php(38): Doctri in /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php on line 136

試行錯誤の末、私は反復可能な結果を使用しない場合、この例外がスローされないことに気づきました。最後に、私はDISTINCTはManyToManyのエンティティとのDQL実行する必要があることが分かりました。

従って私は、上記のPHPコードにDISTINCTを追加し、今ではそれがうまく機能しています。 :)

最終版のDQLは以下のようです：

$dql = 'SELECT DISTINCT posts FROM AcmeBlogBundle:Post posts INNER JOIN posts.tags tag WITH tag.name = :tag WHERE posts.blogId = :blogId';