Doctrine Query Builder nested orX and andX conditi

问题:

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

回答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

回答2:

How about using a simple DQL query with joins instead of complex not in clauses, According to your specified criteria in SQL

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

You could write its equivalent in DQL as

SELECT u
FROM User u
LEFT JOIN u.events nl WITH e.startDate < :now AND e.endDate > :now
LEFT JOIN u.events l WITH e.startDate < :now AND e.endDate > :now AND e.factory = :idf
WHERE (
    u.factory = :idf AND nl.id IS NULL
) OR (
    u.factory != :idf AND l.id IS NOT NULL
)

Join events entity twice with user with additional clauses, one for loaned and other for not loaded and then apply your conditions, I have added IS NULL and IS NOT NULL filters to satisfy your exists and not exists conditions