How to build a Doctrine query which would find elements with missing particular type of OneToMany relation?

Question

let's say we have two entities which are in OneToMany relation, e.g.:

User entity has OneToMany relation to Rewards entity and each Reward may have different type: e.g. 'forOnboarding', 'forReferring', 'forBeingReerred'.

User {
    /**
     * @var Reward[]
     *
     * @ORM\OneToMany(targetEntity="Reward", mappedBy="rewardee"}
     */
    private $rewards;
}


Reward {
    /*
     * @var User
     *
     * @ORM\ManyToOne(targetEntity="User", inversedBy="rewards")
     * @ORM\JoinColumn(nullable=false)
     */
    private $rewardee;

    /** @var string */
    private $type;
}

It's quite easy to find users who have the relation to the exact type of reward, for example:

    public function findUsersWithRewardType(string $rewardType): ?array
    {
        $qb = $this->createQueryBuilder('u')
            ->leftJoin('u.rewards', 'r')
            ->where('r.type = :reward_type')
            ->setParameter('reward_type', $rewardType);
        $query = $qb->getQuery();
        $result = $query->getResult();

        return $result;
    }

Now I want like to build a query which would find all the users who lack a particular type of reward. This is a little bit more tricky so any help would be appreciated!

Due to the nature of OneToMany relation, I cannot just simply change the condition from equal to non-equal, nor I can use the condition where the relation is empty (like on the sample below) because it would not return valid result because users might have a relation with another type of reward.

    public function findUsersWithoutRewardType(string $rewardType): ?array
    {
        $qb = $this->createQueryBuilder('u');
        $qb
            ->leftJoin('u.rewards', 'r')
            ->where('r.type = :reward_type')
            ->setParameter('reward_type', $rewardType)
            ->andWhere('r is NULL');
        $query = $qb->getQuery();
        $result = $query->getResult();

        return $result;
    }

PS: the approach above does not work either way, even if all users lack relation to reward. Probably due to the fact that the property User->rewards is actually ArrayCollection and never set to null.

Thank you!

Answer 1

I found an equivalent solution with query builder as well:

    public function findUsersWithoutRewardType(string $rewardType, $maxResults = 10): ?array
    {
        if (!in_array($rewardType, Reward::TYPE__OPTIONS)) {
            new \Exception(sprintf('Invalid type %s. Possible options are: %s.', $rewardType, implode(',', Reward::TYPE__OPTIONS)));
        }

        $qb = $this->createQueryBuilder('u');
        $qb
            ->where('u.enabled = true')
            ->andWhere($qb->expr()->not($qb->expr()->exists($this->getEntityManager()->createQueryBuilder()->select('r')->from(Reward::class, 'r')->where('u = r.rewardee')->andWhere('r.type = :reward_type')->getDQL())))
            ->orderBy('u.createdAt', 'DESC')
            ->setParameter('reward_type', $rewardType)
            ->setMaxResults($maxResults);
        $query = $qb->getQuery();
        $users = $query->getResult();

        return $users;
    }