Iterate efficiently on large OneToMany - ManyToOne associations

Question

I have two entities Userand Period, they have the ManyToMany association: the user belongs to many periods, and the period has several users. This association uses an object UserPeriod.

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;
}

I am trying to get a list of all users for a certain period. Since there are many users, I cannot load them all in memory and iterate through 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 my own queries, as it Useruses table inheritance.

Answer 1

Github problem

MANY_TO_MANY ONE_TO_MANY , , , .

, , , .

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