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
<?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.](http://qiita.com/items/392a2ff258a3c26adbfe) ;)

-------

# 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
<?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';
```



[Batch Processing]: http://docs.doctrine-project.org/en/2.0.x/reference/batch-processing.html