Doctrine2 named queries

Question

I can't find any documentation about named queries in Doctrine2. Please help. Does Doctrine2 have a named queries feature?

Answer 1

I found this question looking for Native Named Query examples, the above answer helped me so I figured I'd share how to do a native named query in the same way.

Add this to the constructor for your entity repository

public function __construct($em, \Doctrine\ORM\Mapping\ClassMetadata $class)
{
    parent::__construct($em, $class);
    $this->getClassMetadata()->addNamedNativeQuery(
        array('name' => 'published', 
              'query' => 'SELECT 
                  t0.id AS id, 
                  t0.tracking_uri AS tracking_uri, 
                  t0.name AS name, 
                  t0.description AS description, 
                  t0.url_name AS url_name,
                  t0.status_type_id as status_type_id
                FROM 
                  store.store t0 
                WHERE 
                  t0.status_type_id = 2', 
               'resultClass' => '__CLASS__',
               'resultSetMapping' => array('entities' => array('entityClass' => '__CLASS__',
                                                               'fields'      => array('id'           => 'id',
                                                                                      'tracking_url' => 'tracking_url',
                                                                                      'name'         => 'name',
                                                                                      'description'  => 'description',
                                                                                      'url_name'     => 'url_name',
                                                                                      'status_type'  => 'status_type_id')))));
}

Here's a clip of my test for this

public function testNamedNativeQueryPublished()
{
    $results = $this->em->getRepository('MyBundle:Store')->createNativeNamedQuery('published')->execute();

    foreach ($results as $store)
    {
        $this->assertEquals(2, $store->getStatusType());
    }
}

Answer 2

You can use

1. NamedQuery - DQL. Example

   use Doctrine\ORM\Mapping\NamedQuery;
   use Doctrine\ORM\Mapping\NamedQueries;
   
   /**
   * @Entity
   * @Table(name="cms_users")
   * @NamedQueries({
   *     @NamedQuery(name="activated", query="SELECT u FROM __CLASS__ u WHERE u.status = 1")
   * })
   */
   class CmsUser
   {}
   

   And call it like

   $this->getDoctrine()->getRepository('MyBundle:CmsUser')
       ->createNamedQuery('activated')
       ->getResult();
   

2. NamedNativeQuery - SQL. More information here: http://docs.doctrine-project.org/en/latest/reference/native-sql.html#named-native-query

3. Collecting a queries in your EntityRepository, like:

   namespace Acme\StoreBundle\Repository;
   
   use Doctrine\ORM\EntityRepository;
   
   class ProductRepository extends EntityRepository
   {
       public function findAllOrderedByName()
       {
           return $this->getEntityManager()
               ->createQuery('SELECT p FROM AcmeStoreBundle:Product p ORDER BY p.name ASC')
               ->getResult();
       }
   }
   

   More information here: http://symfony.com/doc/current/book/doctrine.html#custom-repository-classes

Similar topic here: https://groups.google.com/forum/?fromgroups#!topic/doctrine-user/K-D5ta5tZ3Y[1-25]

Answer 3

Maybe you'll be interested by the EntityRepositories where you can create and store complex Doctrine queries, and call theme in your project where you want:

http://symfony.com/doc/current/book/doctrine.html#custom-repository-classes

Answer 4

You can call the functions used internally by annotation notation in the constructor of your repository if you don't want to mess up your entity definition:

namespace MyBundle\Repository;

use Doctrine\ORM\EntityRepository;

class CmsUserRepository extends EntityRepository
{
    public function __construct($em, \Doctrine\ORM\Mapping\ClassMetadata $class)
    {
        $this->getClassMetadata()->addNamedQuery(array(
            'name'  => 'activated',
            'query' => 'SELECT u FROM __CLASS__ u WHERE u.status = 1'
        ));
    }
}

You can also replace the __CLASS__ part of the statement with the namespace and class of your entity like MyBundle\Entity\CmsUser