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? — Wouter J
– Wouter J, Commented Jun 26, 2015 at 22:11

geoB · Accepted Answer · 2015-06-24 23:30:35Z

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, 2015 at 23:30

geoB

4,7287 gold badges45 silver badges75 bronze badges

answered Jun 24, 2015 at 21:58

Paul Saunders

2902 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

raygo Over a year ago

where can I add that? There's no place in my code where I have something like that

MECU Over a year ago

Are you referring to the composer.json, config.yml or the actual function you want part?

Paul Saunders Over a year ago

Which section can't you find?

raygo Over a year ago

doctrine: dbal: ... orm: auto_generate_proxy_classes: "%kernel.debug%" entity_managers: default: auto_mapping: true

raygo Over a year ago

@PaulSaunders , I dont have anything like that. That type of configuration. I'm using Zend with Doctrine

|

Collectives™ on Stack Overflow

Doctrine2: Use date functions in QueryBuilder

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related