WJBlog


The Power of Doctrine 2’s Custom Repositories and Native Queries

Published On: April 09, 2011 @ 04:01:12pm


Many novice developers tend to view object-relational mappers (ORMs) as a panacea to all of their database woes, marveling over features such as the convenient object-oriented syntax and magic finders. While there’s no doubt these are pretty cool features, any project of even moderate complexity will quickly exhaust such conveniences, requiring the developer to rely upon more sophisticated approaches to data access. For instance, what if you wanted to use the Haversine formula to calculate the number of registered users which reside within a 15-mile radius of the currently-logged in user? Certainly this isn’t a feature built into the typical ORM, meaning you’ll need to write a custom query to do the job.

Which brings us to the point of this article: where should this query be managed? Because Doctrine 2 treats models as POPOs (plain old PHP objects), it is easy to presume the query should be made available as a method within the model. Don’t make this mistake! Doing so would pollute your application models with database-specific functionality, which is precisely what Doctrine (and most other ORMs) are attempting to avoid altogether. Instead, you can associate a custom repository for each entity, and place the custom query code in the custom repository.

Remember that in Doctrine 2 all interaction with the database flows through the entity manager. The beauty of creating a custom repository is that you will continue to interact with the entity manager, using the custom query as if it were part of Doctrine’s native capabilities. For instance, the typical Doctrine database interaction sequence (when using Doctrine 2 within the Zend Framework) looks like this:

$this->em = $this->_helper->EntityManager();

$account = $this->em->getRepository('Entities\Account')
                    ->findOneByEmail($form->getValue('email'));

Now, suppose you extend the Account entity with a custom repository, and within the custom repository create a finder called getNearbyAccounts(). Once defined, you’ll be able to use that custom finder in exactly the same manner as Doctrine’s other finders:

// Retrieve the nearby accounts
$this->view->accounts = $this->em->getRepository('Entities\Account')
                         ->getNearbyAccounts($account, 10);

Once executed, the $this->view->accounts variable will contain an array of Account entities which represent users residing within a 10-mile radius of the user represented by the entity stored in $account.

Creating a Custom Repository

While Doctrine 2 supports several ways (three to be exact) to define the object-relational map, I’m an advocate of the Docblock annotations approach. Docblock annotations define the map using the same metadata syntax embraced by PHPDocumentor, allowing you to define the map right alongside the entity definition. For instance, here’s a snippet of the Account entity used in the Easy PHP Websites with the Zend Framework‘s companion project GameNomad:

<?php

namespace Entities;
use Doctrine\Common\Collections\ArrayCollection;

/** 
 * @Entity
 * @Table(name="accounts") 
 * @HasLifecycleCallbacks
 */
class Account
{
  /**
   * @Id @Column(type="integer")
   * @GeneratedValue(strategy="AUTO")
   */
  private $id;

  /** @Column(type="string", length=255) */
  private $username;

  /** @Column(type="string", length=255) */
  private $email;

  /** @Column(type="string", length=32) */
  private $password;

  /** @Column(type="decimal", scale=6, precision=10) */
  private $latitude;

  /** @Column(type="decimal", scale=6, precision=10) */
  private $longitude;

  ...

?>

To associate a custom repository with the Account entity, all you have to do is modify the @Entity definition to look like this:

@Entity (repositoryClass="Repositories\Account") 

Next, create the custom repository and add the custom finder, which is presented below. Incidentally, the getNearbyAccounts() method defined in this custom repository uses another fantastic Doctrine 2 feature known as a native query, which allows you to map native SQL queries to Doctrine entities.

<?php

namespace Repositories;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\DBAL\Types\Type;

class Account extends EntityRepository
{
    
  public function getNearbyAccounts($account, $distance)
  {
    
    $rsm = new ResultSetMapping;
    
    $rsm->addEntityResult('Entities\Account', 'a');
    $rsm->addFieldResult('a', 'id', 'id');
    $rsm->addFieldResult('a', 'username', 'username');
    $rsm->addFieldResult('a', 'zip', 'zip');
    $rsm->addFieldResult('a', 'latitude', 'latitude');
    $rsm->addFieldResult('a', 'longitude', 'longitude');
    $rsm->addFieldResult('a', 'confirmed', 'confirmed');    
    
    $query = $this->_em->createNativeQuery(
      "SELECT a.id, a.username, a.zip, a.latitude, a.longitude, a.confirmed, 
        ( 3959 * acos( cos( radians(?) ) * cos( radians( a.latitude ) ) * 
        cos( radians( a.longitude ) - radians(?) ) + sin( radians(?) ) * 
        sin( radians( a.latitude ) ) ) ) AS distance 
        FROM accounts a
        WHERE a.confirmed = 1
        GROUP BY a.id HAVING distance < ? 
        ORDER BY distance", $rsm
    );   
    
    $query->setParameter(1, $account->getLatitude());
    $query->setParameter(2, $account->getLongitude());
    $query->setParameter(3, $account->getLatitude());
    $query->setParameter(4, $distance, Type::INTEGER);
    
    return $query->getResult();
    
  }
  
}

With the custom repository and custom getNearbyAccounts() finder in place, you can begin calling this finder just like you would any other!

Want to learn a whole bunch more about Doctrine 2 and how to integrate it into the Zend Framework? Check out my book (DRM-free PDF), Easy PHP Websites with the Zend Framework (also available in Kindle and Nook formats)!