Converting query including MySQL CONCAT function with three parameters to DQL Query (two parameters)

I'm trying to convert this working MySQL query to DQL query, but I'm facing problem in using the CONCAT function (maybe) in DQL. As I know the CONCAT function in DQL can accept only two parameters, but I need three parameters and after searching I found this solution - NESTED CONCAT:

MySQL query needed to be converted to DQL:

SET @new_booking_pickup_date = '2016-04-01';
SET @new_booking_pickup_time = '12:00:00';
SET @new_booking_return_date = '2016-05-01';
SET @new_booking_return_time = '13:00:00';

SELECT * FROM Reservation WHERE NOT ( 

CONCAT(@new_booking_pickup_date,' ',@new_booking_pickup_time) > CONCAT(return_date,' ',return_time) + INTERVAL 0 DAY 
OR 
CONCAT(@new_booking_return_date,' ',@new_booking_return_time) < CONCAT(pickup_date,' ',pickup_time) + INTERVAL 0 DAY
);

here is the DQL solution what I'm trying with NESTED CONCAT:

return $this->getEntityManager()
    ->createQuery(
        'SELECT id, licensePlate,

        (SELECT car1.model FROM AppBundle:Car car1 WHERE car1.id = car.id) AS model_id,
        (SELECT car2.brand FROM AppBundle:Car car2 WHERE car2.id = car.id) AS brand_id,

        (SELECT carmodel.model FROM AppBundle:CarModel carmodel WHERE carmodel.id = model_id) AS model_name,
        (SELECT carbrand.brand FROM AppBundle:CarBrand carbrand WHERE carbrand.id = brand_id) AS brand_name

          FROM AppBundle:Car car WHERE IDENTITY(car.id) NOT IN (

            SELECT IDENTITY(reservation.car) FROM AppBundle:Reservation reservation WHERE NOT (

              CONCAT(CONCAT(:pickupDate, \' \'),:pickupTime) > CONCAT(CONCAT(reservation.returnDate, \' \'),reservation.returnTime) + INTERVAL 0 DAY 
           OR 
              CONCAT(CONCAT(:returnDate, \' \'),:returnTime) < CONCAT(CONCAT(reservation.pickupDate, \' \'),reservation.pickupTime) + INTERVAL 0 DAY

           )


           )'
    )
    ->setParameter('pickupDate', $pickupDate)
    ->setParameter('returnDate', $returnDate)
    ->setParameter('returnTime', $returnTime)
    ->setParameter('pickupTime', $pickupTime)
    ->getResult();

Here is the result after execution of the DQL query - Exception!!!:

[Syntax Error] line 0, col 831: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '0'

EDIT: Tried this DQL query without any success by proposal of @Alvin Bunk:

return $this->getEntityManager()
        ->createQuery(
            "SELECT car.id AS car_id,

            (SELECT IDENTITY(car1.model) FROM AppBundle:Car car1 WHERE car1.id = car.id) AS model_id,
            (SELECT IDENTITY(car2.brand) FROM AppBundle:Car car2 WHERE car2.id = car.id) AS brand_id,

            (SELECT carmodel.model FROM AppBundle:CarModel carmodel WHERE carmodel.id = model_id) AS model_name,
            (SELECT carbrand.brand FROM AppBundle:CarBrand carbrand WHERE carbrand.id = brand_id) AS brand_name

              FROM AppBundle:Car car WHERE IDENTITY(car.id) NOT IN (

                SELECT IDENTITY(reservation.car) FROM AppBundle:Reservation reservation WHERE NOT (

                  CONCAT(CONCAT(:pickupDate,' '),:pickupTime) > CONCAT(CONCAT(reservation.returnDate,' '),reservation.returnTime) + INTERVAL 0 DAY 
               OR 
                  CONCAT(CONCAT(:returnDate,' '),:returnTime) < CONCAT(CONCAT(reservation.pickupDate,' '),reservation.pickupTime) + INTERVAL 0 DAY

               )


               )"
        )
        ->setParameter('pickupDate', $pickupDate)
        ->setParameter('returnDate', $returnDate)
        ->setParameter('returnTime', $returnTime)
        ->setParameter('pickupTime', $pickupTime)
        ->getResult();

Here I'm using double quotes around the main query and single quotes in CONCAT function. I get the same error message:

[Syntax Error] line 0, col 849: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '0'

EDIT: I'm starting to think the problem is coming from the NESTED CONCAT function, but I tested it in the console and it's working perfect - here is the result:

php app/console doctrine:query:dql "SELECT CONCAT(CONCAT(car.licensePlate,' '),car.deposit) FROM AppBundle:Car car"

array(4) {
  [0]=>
  array(1) {
    [1]=>
    string(10) "B0001HA 20"
  }
  [1]=>
  array(1) {
    [1]=>
    string(10) "B0002HA 20"
  }
  [2]=>
  array(1) {
    [1]=>
    string(10) "B0003HA 30"
  }
  [3]=>
  array(1) {
    [1]=>
    string(10) "B0004HA 40"
  }
}

The solution:

The problem was in (+ INTERVAL 0 DAY) - it's not supported by DQL, in this case I don't even need it because my fields are already of type DATE and TIME.

public function getCarsNotInRange($pickupDate, $pickupTime, $returnDate, $returnTime)
    {
        return $this->getEntityManager()
            ->createQuery(
                "
                SELECT car.id, car.licensePlate,

                (SELECT IDENTITY(car1.model) FROM AppBundle:Car car1 WHERE car1.id = car.id) AS model_id,
                (SELECT IDENTITY(car2.brand) FROM AppBundle:Car car2 WHERE car2.id = car.id) AS brand_id,

                (SELECT carmodel.model FROM AppBundle:CarModel carmodel WHERE carmodel.id = model_id) AS model_name,
                (SELECT carbrand.brand FROM AppBundle:CarBrand carbrand WHERE carbrand.id = brand_id) AS brand_name

                  FROM AppBundle:Car car WHERE car.id NOT IN 
                  (
                    SELECT IDENTITY(reservation.car) FROM AppBundle:Reservation reservation WHERE NOT 
                        (
                          CONCAT(CONCAT(:pickupDate, ' '),:pickupTime) > CONCAT(CONCAT(reservation.returnDate, ' '),reservation.returnTime)
                     OR 
                          CONCAT(CONCAT(:returnDate, ' '),:returnTime) < CONCAT(CONCAT(reservation.pickupDate, ' '),reservation.pickupTime)
                      )
                  )

        ")
            ->setParameter('pickupDate', $pickupDate)
            ->setParameter('returnDate', $returnDate)
            ->setParameter('returnTime', $returnTime)
            ->setParameter('pickupTime', $pickupTime)
            ->getResult();
    }

Solution

The solution:

The problem was in (+ INTERVAL 0 DAY) - it's not supported by DQL, in this case I don't even need it because my fields are already of type DATE and TIME.

public function getCarsNotInRange($pickupDate, $pickupTime, $returnDate, $returnTime)
    {
        return $this->getEntityManager()
            ->createQuery(
                "
                SELECT car.id, car.licensePlate,

                (SELECT IDENTITY(car1.model) FROM AppBundle:Car car1 WHERE car1.id = car.id) AS model_id,
                (SELECT IDENTITY(car2.brand) FROM AppBundle:Car car2 WHERE car2.id = car.id) AS brand_id,

                (SELECT carmodel.model FROM AppBundle:CarModel carmodel WHERE carmodel.id = model_id) AS model_name,
                (SELECT carbrand.brand FROM AppBundle:CarBrand carbrand WHERE carbrand.id = brand_id) AS brand_name

                  FROM AppBundle:Car car WHERE car.id NOT IN 
                  (
                    SELECT IDENTITY(reservation.car) FROM AppBundle:Reservation reservation WHERE NOT 
                        (
                          CONCAT(CONCAT(:pickupDate, ' '),:pickupTime) > CONCAT(CONCAT(reservation.returnDate, ' '),reservation.returnTime)
                     OR 
                          CONCAT(CONCAT(:returnDate, ' '),:returnTime) < CONCAT(CONCAT(reservation.pickupDate, ' '),reservation.pickupTime)
                      )
                  )

        ")
            ->setParameter('pickupDate', $pickupDate)
            ->setParameter('returnDate', $returnDate)
            ->setParameter('returnTime', $returnTime)
            ->setParameter('pickupTime', $pickupTime)
            ->getResult();
    }