Doctrine2: Use date functions in QueryBuilder

Question

I want to know how to use date functions like WEEK or MONTH in query builder. I'm using Zend not Symfony. When I try my current code with WEEK I get this error:

Error: Expected known function, got 'WEEK'

This is my current code:

<?php
  namespace Repositories;
  use Doctrine\ORM\EntityRepository;
  /**
   * Analytic
   *
   * This class was generated by the Doctrine ORM. Add your own custom
   * repository methods below.
   */
  class Analytic extends EntityRepository {
    public function getSocialAnalytics($type){
      $response = false;
      if($type){
        $qb = $this->_em->createQueryBuilder();
        $qb
          ->select('a')
          ->from('\Entities\Analytic', 'a');
        $qb->where(' a.type = :type ');
        $qb->andWhere(' WEEK(NOW()) = WEEK(created) ');
        $qb->setParameter('type', $type);
        $response =  $qb->getQuery()->getResult(\Doctrine\ORM\Query::HYDRATE_OBJECT);
      }
      return $response;
    }
  }

This is my configuration:

// Custom resource plugins inherit this sweet getOptions() method which will retrieveenter code here
          // configuration settings from the application.ini file
      $config = new Zend_Config($this->getOptions());

      // Define the connection parameters
      $options = array(
          'connection' => array(
          'driver'   => "{$config->connection->driver}",
          'host'     => "{$config->connection->host}",
          'dbname'   => "{$config->connection->dbname}",
          'user'     => "{$config->connection->user}",
          'password' => "{$config->connection->password}"
        )
      );

      $configEm = new \Doctrine\ORM\Configuration;

      $cache = new \Doctrine\Common\Cache\ArrayCache;

      $driverImpl = $configEm->newDefaultAnnotationDriver(
        $config->connection->entities
      );

      $configEm->setMetadataCacheImpl($cache);

      $configEm->setMetadataDriverImpl($driverImpl);      

      // Configure proxies

      $configEm->setAutoGenerateProxyClasses(
        $config->connection->proxies->generate
      );      

      $configEm->setProxyNamespace($config->connection->proxies->ns);      

      $configEm->setProxyDir(
        $config->connection->proxies->location
      );      

      // Configure cache

      $configEm->setQueryCacheImpl($cache);

      $em = \Doctrine\ORM\EntityManager::create($options['connection'], $configEm);
      Zend_Registry::set('em', $em);

      return $em;

possible duplicate of [How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?](http://stackoverflow.com/questions/18826836/how-can-i-use-sqls-year-month-and-day-in-doctrine2) — Wouter J, Jun 26 '15 at 22:11

score 5 · Accepted Answer · edited Jun 24 '15 at 23:30

5

You can use https://github.com/beberlei/DoctrineExtensions. Add it to your composer.json and update your config.yml as below

doctrine:
    dbal:
        ...
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        entity_managers:
          default:
            auto_mapping: true
            dql:
              datetime_functions: 
                Year: DoctrineExtensions\Query\Mysql\Year

edit. The actual function you want:

WEEK: DoctrineExtensions\Query\Mysql\Week

edited Jun 24 '15 at 23:30

geoB

4,578
5
37
70

answered Jun 24 '15 at 21:58

Paul Saunders

278
2
10

where can I add that? There's no place in my code where I have something like that – raygo Jun 24 '15 at 22:03
Are you referring to the `composer.json`, `config.yml` or the __actual function you want__ part? – MECU Jun 25 '15 at 02:15
Which section can't you find? – Paul Saunders Jun 25 '15 at 07:41
doctrine: dbal: ... orm: auto_generate_proxy_classes: "%kernel.debug%" entity_managers: default: auto_mapping: true – raygo Jun 25 '15 at 18:47
@PaulSaunders , I dont have anything like that. That type of configuration. I'm using Zend with Doctrine – raygo Jun 25 '15 at 18:48
Your question is tagged with Symfony2 which is why I provided symfony config in my answer (You should mark it as answered :D). You need to set it manually using: $configEm->addCustomDatetimeFunction('WEEK', 'DoctrineExtensions\Query\Mysql\Week'); This will ONLY work if you've installed the DoctrineExtensions dependency correctly. You should really ask a new question including your config (because this one has been answered) – Paul Saunders Jun 26 '15 at 08:23

Doctrine2: Use date functions in QueryBuilder

1 Answers1