Doctrine - not In

Question

I am making a car reservation app for my university class. I have reservation stored in db and I want to query for cars that ara available in given period of time.

So, I have created custom repository class that looks like this:

use Doctrine\ORM\EntityRepository;

class CarRepository extends EntityRepository
{
    public function findAllAvailable($pickupDate, $returnDate)
    {
        $subQueryBuilder = $this->getEntityManager()->createQueryBuilder();
        $carsInOverlapingPeriod = $subQueryBuilder
            ->select('rv')
            ->from('AppBundle:Reservation', 'rv')
            ->where(':pickup_date < rv.returnDateTime AND :return_date > rv.pickupDateTime')
            ->setParameters(array(
                'pickup_date' => $pickupDate,
                'return_date' => $returnDate
            ))
            ->getQuery()
            ->getArrayResult()
        ;

        $queryBuilder = $this->getEntityManager()->createQueryBuilder();
        $query = $queryBuilder
            ->select(['car'])
            ->from('AppBundle:Car', 'car')
            ->where($queryBuilder->expr()->notIn('car.id', ':subQuery'))
            ->setParameter('subQuery', $carsInOverlapingPeriod)
            ->getQuery()
        ;

        return $query->getResult();
    }
}

$carsInOverlapingPeriod returns array of reservations that happen in given period of time, $query should return only cars that are available.

In reservation table I have field/column named car_id (In reservation entity it's $carId).

Can't figure out how to build 'where' condition in $query.

I guess it should look something like:

WHERE car.id NOT IN subquery WITH car.id = rv.car_id

Answer 1

You can make the subselect only with the id of the car you can't select in se main query.

You can put directly the result DQL statement as subquery (instead of execute it and put the array of ids) as follow:

public function findAllAvailable($pickupDate, $returnDate)
{
    $subQueryBuilder = $this->getEntityManager()->createQueryBuilder();
    $subQuery = $subQueryBuilder
        ->select('IDENTITY(rv.carId)')   // see above comment for further explanation about identity
        ->from('AppBundle:Reservation', 'rv')
        ->where(':pickup_date < rv.returnDateTime AND :return_date > rv.pickupDateTime')
        ->setParameters(array(
            'pickup_date' => $pickupDate,
            'return_date' => $returnDate
        ));

    $queryBuilder = $this->getEntityManager()->createQueryBuilder();
    $query = $queryBuilder
        ->select(['car'])
        ->from('AppBundle:Car', 'car')
        ->where($queryBuilder->expr()->notIn('car.id', $subQuery->getQuery()->getDQL() ) )
        ->getQuery()
    ;

    return $query->getResult();
}

Hope this help