Iterate efficiently on large OneToMany – ManyToOne associations

Question

I have two entities User and Period, they have a ManyToMany association: a user belongs to many periods, and a period has multiple users. This association uses a UserPeriod entity.

class Period
{
    /**
     * @ORM\OneToMany(targetEntity="UserPeriod", mappedBy="period")
     */
    private $users;
}

class UserPeriod
{
    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="User", inversedBy="periods")
     */
    private $user;

    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Period", inversedBy="users")
     */
    private $period;
}

class User extends BaseUser
{
    /**
     * @ORM\OneToMany(targetEntity="UserPeriod", mappedBy="user")
     */
    protected $periods;
}

What I'm trying to achieve is getting a list of all users from a defined period. Since there is a lot of users, I can't load them all in memory and must iterate on them (batch processing). Here is what I tried:

public function getUsersOfQuery($period)
{
    return $this->_em->createQueryBuilder()
                ->select('u')
                ->from('SGLotteryUserBundle:LotteryUser', 'u')
                ->innerJoin('u.periods', 'p')
                ->where('p.period = :id')
                ->setParameter('id', $period->id())
                ->getQuery();
}

$it = $repo->getUsersOfQuery($period)->iterate();

But, this exception is raised:

[Doctrine\ORM\Query\QueryException]                                                                           
Iterate with fetch join in class UserPeriod using association user not allowed.

I cannot use native queries since User uses table inheritance.

Answer 1

Github issue

This happens when using either MANY_TO_MANY or ONE_TO_MANY join in your query then you cannot iterate over it because it is potentially possible that the same entity could be in multiple rows.

If you add a distinct to your query then all will work as it will guarantee each record is unique.

$qb = $this->createQueryBuilder('o');
$qb->distinct()->join('o.manyRelationship');
$i = $qb->iterator;
echo 'Profit!';