How to make a DQL query in a OneToMany relation that exclude elements at a certain date?

Question

I have a problem with a query in Symfony. I have two entities with a OneToMany relation:

/**
 * Field
 *
 * @ORM\Table(name="field")
 * @ORM\Entity(repositoryClass="MyBundle\Repository\FieldRepository")
 */
class Field
{
    .
    .
    .
    /**
    * @var string
    *
    * @ORM\Column(name="name", type="string", length=255)
    */
   private $name;

     /**
     * @ORM\OneToMany(targetEntity="Booking", mappedBy="field", cascade={"remove","persist"})
     */
    private $bookings;

And:

/**
 * Booking
 *
 * @ORM\Table(name="booking")
 * @ORM\Entity(repositoryClass="MyBundle\Repository\BookingRepository")
 */
class Booking
{
    .
    .
    .

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date", type="datetime", nullable = true)
     */
    private $date;

    /**
     * @ORM\ManyToOne(targetEntity="Field", inversedBy="bookings")
     * @ORM\JoinColumn(name="field_id", referencedColumnName="id")
     */
    private $field;

And I want to get the fields that are free (NOT booked) at certain date. I tried this:

public function findFreeFields($date){

        $em = $this->getEntityManager();

        $queryText = "SELECT f, FROM MyBundle:Field f JOIN f.bookings b ";
        $queryText .= "WHERE  b.date!=:date";

        $query = $em->createQuery($queryText);
        $query->setParameter('date', $date);
        return $query->getResult();

The problem is that if, for instance, I have a field called "field1" with 4 bookings and one of those bookings is for the date 20/05/2017. I look for all the fields free at that date (20/05/2017) with findFreeFields(), but I get a list with "field1" repeated three times, which is obviously wrong. The "field1" must not be in the result list because it is booked at the specified date.

With DISTINCT I don't get repeated results but still get "field1" in the result.

public function findFreeFields($date){

        $em = $this->getEntityManager();

        $queryText = "SELECT DISTINCT f, FROM MyBundle:Field f JOIN f.bookings b ";
        $queryText .= "WHERE  b.date!=:date";

        $query = $em->createQuery($queryText);
        $query->setParameter('date', $date);
        return $query->getResult();

So, how do I have to write the DQL query in order to exclude the fields that have bookings at certain date?

Answer 1

The solution is to do a subquery which selects all Fileds with a booking for specified date and then use that subquery to filter out selection of all Fields.

I used QueryBuilder and divided subquery & query for clarity, but feel free to modify anything according to your preferences.

...
$em = $this->getDoctrine()->getManager();

$qb = $em->createQueryBuilder();
$bookedFieldsQuery = $qb->select('bookedf')
                        ->from('MyBundle:Field', 'bookedf')
                        ->join('bookedf.bookings', 'b', 'WITH' ,'b.date = :date')
                        ->getDQL();

$qb = $em->createQueryBuilder();
$availableFieldsQuery = $qb->select('f')
                           ->from('MyBundle:Field', 'f')
                           ->where($qb->expr()->notIn('f', $bookedFieldsQuery))
                           ->getQuery();

$availableFields->setParameter('date', $date);

return $availableFieldsQuery->getResult();
...

The final DQL itself:

SELECT f FROM MyBundle:Field f WHERE f.id NOT IN(SELECT bookedf.id FROM MyBundle:Field bookedf INNER JOIN bookedf.bookings b WITH b.date = :date)