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;

Answer 1

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