How to use DATE_ADD in Doctrine's Querybuilder?

Question

I am working on a repo to find all booking done in a reservation during a period. I have a check-in date and a number of nights they stay.

/*
* @return array
*/
public function findAllByHotelIdAndDate(int $id, \DateTime $date)
{

    $qb = $this->createQueryBuilder('re')
        ->leftJoin(Room::class, 'ro')
        ->leftJoin(Hotel::class, 'h')
        ->where('h.id =:idHotel')
        ->andWhere('re.checkIn >= :date')
        ->andWhere("DATE_ADD(re.checkIn, re.nights, 'day') <= :date")
        ->setParameters(['date' => $date, 'idHotel' => $id])
        ;

    $query = $qb->getQuery();
    return $query->execute();
}

My issue is on the use of DATE_ADD. Is my syntax correct?

I can't edit my question but I got an error message: SQLSTATE[42000]: Syntax error or access violation: 1064 Erreur de syntaxe près de 'WHERE h4_.id = 1 AND r0_.check_in >= '2021-06-03 00:00:00' AND '2021-06-03 00:00' à la ligne 1 when I execute this query — Sam Bath, Jun 08 '21 at 11:28
Or this one https://stackoverflow.com/questions/23301179/how-to-use-date-add-to-compare-date-in-range-in-doctrine-and-symfony2-using-crea — yivi, Jun 08 '21 at 12:08
Hi, thanks @yivi. It's the 2 questions I have been through at first but I still have the issue. I tried to put my variable before the DATE_ADD, and no ameliorations. I don't see the issue with my query. I tried with single quote, double quote — Sam Bath, Jun 08 '21 at 12:10
The Edit link is under the tags at the bottom of your question. No particular reason why you should not be able to edit it. — Cerad, Jun 08 '21 at 12:17

Florent Cardot · Answer 1 · 2021-06-08T13:43:24.947

To use DATE_ADD you will have to add the doctrine extensionin your doctrine config (which you seems to have already done, but for reminder here it is):

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        server_version: '5.6'
        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '13'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

        string_functions:
            DATE: DoctrineExtensions\Query\Mysql\Date
            DATE_FORMAT: DoctrineExtensions\Query\Mysql\DateFormat

You will need to instal Doctrine extension (see https://github.com/doctrine-extensions/DoctrineExtensions) Also you can add your own dql function (see the doc: https://symfony.com/doc/current/doctrine/custom_dql_functions.html)

Then the format to use it is the one you did:

                    ->where("DATE_ADD(u.date,u.old,'day') <  :now")

where u.date is the user creationDate by exemple and u.old is the user age

->where("DATE_ADD(DATTIME,INTEGER,'day') <  PARAMETER")

merci, I put it in my doctrine.yaml but I get an error message "Unrecognized option "dql" under "doctrine". Did you mean "dbal"? and if change it by dbal, I get a "duplicate key" for dbal mention "Duplicate key "dbal" detected at line 19 (near " DATE_FORMAT:" and if I put it below server_version, it doesn't work either. I put my yaml on codeshare: https://codeshare.io/9OyYM1 . — Sam Bath, Jun 08 '21 at 12:24
I edited my answer with your yaml, and added some documentation if you want to know more about it — Florent Cardot, Jun 08 '21 at 13:44

How to use DATE_ADD in Doctrine's Querybuilder?

1 Answers1