Doctrine 2 has a very powerful database abstraction layer and features an Object Relational Mapper (ORM) that makes it very easy to manage relationships between the data in your application and provides tools that aid rapid application development (RAD).
With Doctrine also comes DQL, a powerful language for querying your object models, which is similar to SQL.
Use of Doctrine abstracts away the database concepts of tables and columns, rather you think in terms of objects, entities, properties and relationships. While this is very useful, it is very important for the developer to have an understanding of the underlying SQL that is generated from using the entity manager/DQL and querying your objects/entities.
Without an understanding of the code that Doctrine generates, it is relatively easy to build an inefficient and slow application, here are a few pitfalls that I have found are easy to fall into, and some tips that may be useful for people starting with Doctrine, or if you are trying to get a little more performance out of an existing application.
For the rest of this post I will use the cookie cutter example of a Blog website with article, user and comment object models (entities). In terms of object mapping we will assume all mapping is specified with the default (lazy) fetch method, and MySQL as our DB.
1. Always write a DQL statement for querying your object models
Doctrine repositories provide some ready made methods for fetching your objects such as “->findAll()” and “->findBy()”. These provide some convenience and brevity when initially developing your application but use of these functions is less flexible when further developing your application and can lead to some of the pitfalls below.
By writing the DQL yourself, you will expand your understanding of Doctrine, you have more control of the data you are trying to query and people reading your code will have a better understanding of what you are trying to do (and will find it easier to update and extend your code).
2. Beware of lazy loading when querying entities with associations
If you do not specify an ‘eager’ fetch in your object mapping, or do not explicitly fetch your relationships in your DQL statements (or if you query your objects using the Doctrine methods mentioned in point 1) Doctrine will not fetch those associations in the SQL it generates. This is helpful as you do not want to be loading resources you do not need, however when you call a related object, Doctrine needs a method of fetching these objects and it does this by lazy loading through proxies.
For example if you have an article entity, with associated comments and you want to display a list of all your articles with number of comments on the front page of your blog, you may do something like this (this code is purely for demonstration).
$articles = $article_repository->findAll();
foreach($articles as $article)
{
echo $article->getTitle();
echo count($article->getComments());
}
Using this code, Doctrine will initially generate a query for all the rows in the article table. As we have not ‘fetched’ the comment association, Doctrine will lazy load in the comment object for each article via an additional SQL statement. For a table containing 10 rows, this will result in 11 database queries.
//$em is the entity manager
$qb = $em->createQueryBuilder();
$qb->select('Article', 'Comment')
->from('Entity\Article', 'Article')
->leftJoin('Article.comment', 'Comment');
$query = $qb->getQuery();
$result = $query->getResult();
return $result;
Instead, by using DQL and specifying our relationships, we can use the above code to fetch the same data with 1 query vs the 11 (unnecessary) database queries generated previously. This problem grows with each additional row in a given database.
3. Use array hydration for read only actions
The Doctrine documentation recommends the use of array hydration for read only actions, this is in part due to the extra overhead (increased memory needed from loading proxies and the like) associated with hydrating objects.
If you are just displaying lists of items, or lists of entity properties for a single item (for example a product page, a list of recent blog articles, or indeed most front end operations!) you can use:
$query->getResult(Query::HYDRATE_ARRAY);
This approach can also prevent any accidental lazy-loading problems that may arise as discussed previously (due to the lack of proxies!)
4. By default Doctrine will fetch all of the properties for a given entity
This doesn’t exactly fall under a best practice, indeed making any changes here too early in the development cycle would fall under premature optimisation. Nonetheless consider the following:
$qb->select('Article')
->from('Entity\Article');
It is somewhat wasteful in terms of resources to fetch data from your database that you aren’t going to use. The above code snippet would fetch all the properties for the article entity.
Rather than selecting the whole object, in DQL you can specify specific properties to return in your select statement:
$qb->select('Article.title')
->from('Entity\Article');
However this will always return a flat array, regardless of relationships between your entities. Instead you can select and hydrate partial objects in your DQL, which will give you an object or a nested array representing your object graph (depending on the hydration selected)
$query = $em->createQuery("SELECT partial Article.{title} Entity\Article Article");
As stated in the Doctrine documents it is important to note here that this optimisation can lead to more fragile code, it may not always be clear (especially if working as part of a team, and working code written by others) that object properties have not been fetched.
5. Use prepared statements
Using prepared statements helps to prevent SQL injection attacks that have proven problematic for developers and businesses alike – Sony notably exposed a lot of user data in Summer 2011. See below for a quick example of how to use prepared statements in Doctrine.
$qb = $em->createQueryBuilder();
$qb->select('Article', 'Comment')
->from('Entity\Article', 'Article')
->leftJoin('Article.comment', 'Comment')
->where($qb->expr()->eq('Article.title', ':filter_title')
->setParameter('filter_title', $some_user_input);
$query = $qb->getQuery();
$result = $query->getResult();
return $result;
The Doctrine documentation provides a more in depth explanation for achieving this.
6. Be aware of what Doctrine is up to
If you are using Symfony 2, you can use the in-built profiler for monitoring the number of queries generated by doctrine. If you are using Zend Framework you can refer to my previous blog post covering some methods for profiling doctrine 1 or 2.
I hope these tips prove helpful if you are just getting started with Doctrine, or if you have been using it for a little while. If you have any tips of your own let us know on twitter @uv_d or leave a comment
This post was written by Pete on January 6, 2012 at 6:49 pm
