Doctrine Query Builder nested orX and andX conditions with join

Question

I have two entities User and CalendarEvent. My users are attached to a factory, and calendarEvent can be used to know if a user is "loaned" to a different factory of the one he belongs to...

My two entities are like this :

User :

class User extends BaseUser
{

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var string
     * @ORM\Column(name="firstname", type="string", length=255, nullable=true)
     */
    private $firstname;

    /**
     * @var string
     * @ORM\Column(name="lastname", type="string", length=255, nullable=true)
     */
    private $lastname;

    /**
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\CalendarEvent", mappedBy="user")
     */
    private $events;

    /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Factory", inversedBy="users")
     * @ORM\JoinColumn(name="factory_id", referencedColumnName="id")
     */
    private $factory;

}

CalendarEvent:

class CalendarEvent
{

    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id; 

    /**
     * @var \DateTime
     * @ORM\Column(type="datetime", name="event_start")
     */
    private $startDate;

    /**
     * @var \DateTime
     * @ORM\Column(type="datetime", name="event_end")
     */
    private $endDate;

    /**
     * @var bool
     * @ORM\Column(name="isLoan", type="boolean")
     */
    private $isLoan = TRUE;

    /**
     * @ORM\ManyToOne(targetEntity="Factory")
     * @ORM\JoinColumn(name="factory_id", referencedColumnName="id", nullable = true)
     */
    private $factory;

    /**
     * @ORM\ManyToOne(targetEntity="UserBundle\Entity\User", inversedBy="events")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */
    private $user;
}

What i'm trying to do is create a repository function in the UserRepository which gets me the people which actualy are is the factory when I trigger the request with the factory in entry argument...

In order to achieve that, I try to specify a condition. That condition should looks like that :

select * from user where (
    (user.factory == $idfactory AND not loaned_to_other_factory) OR 
    (user.factory != $idfactory AND loaned_to_my_factory)
)

To know if a user has been loaned to my factory, the following condition must be verified :

Does a record exist in CalendarEvent where startDate < NOW and endDate > NOW and loaned == true and factory == $factory

I'm trying to user and / or conditions with my repository but I've been stuck with that query for so long, i'm not even used to work with repository as the findBy method works fine in most of the cases...

I tried this but I have an error Expression of type 'Doctrine\ORM\QueryBuilder' not allowed in this context. for this variable $notLoanedToOther :

public function findByFactory($idFactory)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('u')
    ->from($this->_entityName, 'u')
    ->leftJoin('u.events', 'e');

    $sub = $this->createQueryBuilder("e");
    $sub->select("e");
    $sub->from("CalendarEvent","e");
    $sub->where('e.user = u.id');
    $sub->andWhere('e.startDate < :now');
    $sub->andWhere('e.endDate > :now');

    $notLoanedToOther = $qb->where($qb->expr()->not($qb->expr()->exists($sub->getDQL())));

    $sub = $this->createQueryBuilder("e");
    $sub->select("e");
    $sub->from("CalendarEvent","e");
    $sub->where('e.user = u.id');
    $sub->where('e.factory = :idf');
    $sub->andWhere('e.startDate < :now');
    $sub->andWhere('e.endDate > :now');

    $loanedToMyFactory = $qb->where($qb->expr()->exists($sub->getDQL()));

    $condition1 = $qb->expr()->andX(
        $qb->expr()->eq('u.factory', ':idf'),
        $notLoanedToOther
    );

    $condition2 = $qb->expr()->andX(
        $qb->expr()->neq('u.factory', ':idf'),
        $loanedToMyFactory
    );

    $qb ->where($qb->expr()->orX($condition1, $condition2));

    $qb ->setParameter('idf', $idFactory);

    return $qb->getQuery()->getResult();
}

I realy hope I made sense and someone can give me tips to achieve that. Thanks in advance

Answer 1

I guess you could translate your exists query to not in

$notLoanedToOther = $this->createQueryBuilder("e")
                         ->select("e.user")
                         ->from("CalendarEvent","e")
                         ->andWhere('e.startDate < :now')
                         ->andWhere('e.endDate > :now')
                         ->getDQL();

$loanedToMyFactory = $this->createQueryBuilder("e")
                          ->select("e.user")
                          ->from("CalendarEvent","e")
                          ->where('e.factory = :idf')
                          ->andWhere('e.startDate < :now')
                          ->andWhere('e.endDate > :now')
                          ->getDQL();

$qb = $this->_em->createQueryBuilder();
 $qb->select('u')
    ->from($this->_entityName, 'u')
    ->where(
        $qb->expr()->not(
            $qb->expr()->in(
            'u.id',
            $notLoanedToOther
            )
        )
    )
    ->andWhere(
        $qb->expr()->in(
        'u.id',
        $loanedToMyFactory
        )   
    )
    ->where(
        $qb->expr()->orX(
                $qb->expr()->andX(
                    $qb->expr()->eq('u.factory', ':idf'),
                    $qb->expr()->not(
                        $qb->expr()->in(
                        'u.id',
                        $notLoanedToOther
                        )
                    )
                ), 
                $qb->expr()->andX(
                    $qb->expr()->neq('u.factory', ':idf'),
                    $qb->expr()->in(
                    'u.id',
                    $loanedToMyFactory
                    )
                )
            )
        )
    ->setParameter('idf', $idFactory)
    ->setParameter('now', $someDate)
    ;

Doing a WHERE .. IN subquery in Doctrine 2