Comparing Propel, Doctrine and sfPropelFinder

When it comes to ORMs, it's all a matter of preference. Is it, really? This post compares side-by-side the code required to perform some simple operations with three OO database requesting API. The purpose is to demonstrate that productivity, and not only style, can vary a lot depending on the ORM you choose.

There are not many robust Object Relational Mapping layers in PHP5. I'll consider two of them:

• Propel is an ORM that "allows you to access your database using a set of objects, providing a simple API for storing and retrieving data. Propel allows you, the web application developer, to work with databases in the same way you work with other classes and objects in PHP."

• Doctrine is an ORM that "sits on top of a powerful PHP DBAL (database abstraction layer). One of its key features is the ability to optionally write database queries in an OO (object oriented) SQL-dialect called DQL inspired by Hibernates HQL. This provides developers with a powerful alternative to SQL that maintains a maximum of flexibility without requiring needless code duplication."

I will also consider an additional component to Propel named sfPropelFinder. It "provides an easy API for finding Propel objects - that is, easier than the Peer methods and the Criteria stuff". sfPropelFinder is a symfony plugin, but it can be used with Propel alone.

For the examples, I'll use the classic Article/Comment model.

Disclaimer: Being the author of sfPropelFinder, you may think that I chose examples that make it look better. To avoid this bias, I wrote a lot of examples, including some where this plugin does not perform very well. Still, if the sfPropelFinder comparison with the two other ORMs is not objective, the comparison between Propel and Doctrine is quite so.

Scope

This comparison will only focus on the API - I voluntarily leave the performance benchmarks to whoever wants to do it. But I think the gross performance comparison probably looks like:

 Slowest    sfPropelFinder + Propel 1.2 |          Propel 1.2 |          Doctrine 0.11 |          sfPropelFinder + Propel 1.3 Fastest    Propel 1.3 

As for the features, it is hard to give an objective comparison without getting too much in the details. If you wonder if a particular ORM does something that another can't do, post a comment about it and I'll try to give you an honest answer.

Bear in mind that sfPropelFinder is very young, that Doctrine is quite young, and that Propel has a longer history and is the most stable and mature of all three.

Retrieving an article by its primary key

// Propel
$article = ArticlePeer::retrieveByPk(123);
// Doctrine
$article = Doctrine::getTable('Article')->find(123);
// sfPropelFinder
$article = sfPropelFinder::from('Article')->findPk(123);

Retrieving the comments related to an article

// Propel
$comments = $article->getComments();
// Doctrine
$comments = $article->Comments;
// sfPropelFinder
$comments = $article->getComments(); // no change - use Propel

Retrieving an article from its title

// Propel
$c = new Criteria();
$c->add(ArticlePeer::TITLE, 'FooBar');
$article = ArticlePeer::doSelectOne($c);

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  where('a.title = ?', array('FooBar'))->
  fetchOne();
// Doctrine (faster)
$article = Doctrine::getTable('Article')->
  findOneByTitle('FooBar');

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  where('Title', 'FooBar')->
  findOne();
// sfPropelFinder (faster)
$article = sfPropelFinder::from('Article')->
  findOneByTitle('FooBar');

Retrieving the latest 5 articles

// Propel
$c = new Criteria();
$c->addDescendingOrderByColumn(ArticlePeer::PUBLISHED_AT);
$c->setLimit(5);
$articles = ArticlePeer::doSelect($c);

// Doctrine
$articles = Doctrine_Query::create()->
  from('Article a')->
  orderby('a.published_at DESC')->
  limit(5)->
  execute();

// sfPropelFinder
$articles = sfPropelFinder::from('Article')->
  orderBy('PublishedAt', 'desc')->
  find(5);

Retrieving the last 5 comments related to an article

// Propel
$c = new Criteria();
$c->addDescendingOrderByColumn(CommentPeer::PUBLISHED_AT);
$c->setLimit(5);
$comments = $article->getComments($c);

// Doctrine
$comments = Doctrine_Query::create()->
  from('Comment c')->
  where('c.article_id = ?', array($article->getId()))->
  orderby('c.published_at DESC')->
  limit(5)->
  execute();

// sfPropelFinder
$comments = sfPropelFinder::from('Comment')->
  relatedTo($article)->
  orderBy('PublishedAt', 'desc')->
  find(5);

Retrieving the last comment related to an article

// Propel
$c = new Criteria();
$c->addDescendingOrderByColumn(CommentPeer::PUBLISHED_AT);
$c->add(CommentPeer::ARTICLE_ID, $article->getId());
$comment = CommentPeer::doSelectOne($c);

// Doctrine
$comments = Doctrine_Query::create()->
  from('Comment c')->
  where('c.article_id = ?', array($article->getId()))->
  orderby('c.published_at DESC')->
  fetchOne();

// sfPropelFinder
$comments = sfPropelFinder::from('Comment')->
  relatedTo($article)->
  findLast();

Retrieving articles based on a word appearing in the title or the summary

// Propel
$c = new Criteria();
$cton1 = $c->getNewCriterion(ArticlePeer::TITLE, '%FooBar%', Criteria::LIKE);
$cton2 = $c->getNewCriterion(ArticlePeer::SUMMARY, '%FooBar%', Criteria::LIKE);
$cton1->addOr($cton2);
$c->add($cton1);
$articles = ArticlePeer::doSelect($c);

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  where('a.title like ? OR a.summary like ?', array('%FooBar%', '%FooBar%'))->
  execute();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  where('Title', 'like', '%FooBar%')->
  _or('Summary', 'like', '%FooBar%')->
  find();

Retrieving articles based on a complex AND/OR clause

// Articles having name or summary like %FooBar% and published between $begin and $end

// Propel
$c = new Criteria();
$cton1 = $c->getNewCriterion(ArticlePeer::TITLE, '%FooBar%', Criteria::LIKE);
$cton1 = $c->getNewCriterion(ArticlePeer::SUMMARY, '%FooBar%', Criteria::LIKE);
$cton1->addOr($cton2);
$c->add($cton1);
$c->add(ArticlePeer::PUBLISHED_AT, $begin, Criteria::GREATER_THAN);
$c->addAnd(ArticlePeer::PUBLISHED_AT, $end, Criteria::LESS_THAN);
$article = ArticlePeer::doSelect($c);

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  where('(a.title like ? OR a.summary like ?) and (article.published_at> ? and article.published_at> ?)', array('%FooBar%', '%FooBar%', $begin, $end))->
  execute();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
    where('Title', 'like', '%FooBar%', 'cond1')->
    where('Summary', 'like', '%FooBar%', 'cond2')->
   combine(array('cond1', 'cond2'), 'or', 'cond3')->
    where('PublishedAt', '>', $begin, 'cond4')->
    where('PublishedAt', '<', $end, 'cond5')->
   combine(array('cond4', 'cond5'), 'and', 'cond6')->
  combine(array('cond3', 'cond6'), 'and')->
  find();

Retrieving articles authored by someone

// Propel
$c = new Criteria();
$c->addJoin(ArticlePeer::AUTHOR_ID, AuthorPeer::ID);
$c->add(AuthorPeer::NAME, 'John Doe');
$articles = ArticlePeer::doSelect($c);

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  leftJoin('a.Author b')->
  where('b.name = ?', array('John Doe'))->
  execute();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  where('Author.Name', 'John Doe')-> // Guesses the join from the schema
  find();

Retrieving articles authored by people of a certain group

// Propel
$c = new Criteria();
$c->addJoin(ArticlePeer::AUTHOR_ID, AuthorPeer::ID);
$c->addJoin(AuthorPeer::GROUP_ID, GroupPeer::ID);
$c->add(GroupPeer::NAME, 'The Foos');
$articles = ArticlePeer::doSelect($c);

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  leftJoin('a.Author b')->
  leftJoin('b.Group c')->
  where('c.name = ?', array('The Foos'))->
  execute();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  join('Author')->
  where('Group.Name', 'The Foos')-> // Guesses the Group join from the schema
  find();

Retrieving all articles and hydrating their category object in the same query

// Propel
$c = new Criteria();
$articles = ArticlePeer::doSelectJoinCategory($c);

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  leftJoin('a.Category c')->
  execute();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  with('Category')->
  find();

Retrieving an article and its category by the article primary key

// Propel
$c = new Criteria();
$c->add(ArticlePeer::ID, 123);
$c->setLimit(1);
$articles = ArticlePeer::doSelectJoinCategory($c);
$article = isset($articles[0]) ? $articles[0] : null;

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  leftJoin('a.Category c')->
  where('a.id = ?', array(123))->
  fetchOne();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  with('Category')->
  findPk(123);

Retrieving articles and hydrating their author object and the author group

// Propel
// Impossible do to it simply - need for a custom hydration method (approx 40 LOC)

// Doctrine
$article = Doctrine_Query::create()->
  from('Article a')->
  leftJoin('a.Author b')->
  leftJoin('b.Group c')->
  where('a.id = ?', array(123))->
  fetchOne();

// sfPropelFinder
$article = sfPropelFinder::from('Article')->
  with('Category', 'Group')->
  findPk(123);

Conclusion

That's a lot of queries. And I didn't mention many-to-many relations, addition of columns, behaviors, update/delete queries, count queries, or pagers. But overall, my conclusion after writing these examples is:

• Propel is the most verbose ORM of all three
• sfPropelFinder is the most magic of all three
• sfPropelFinder and Doctrine are the fastest to write, depending on the cases
• Some limits of Propel are very frustrating (limited doSelectJoinXXX(), Criterions, custom hydration)
• Propel and sfPropelFinder will never beat DQL for complex queries

Finally, if you are wondering which ORM to choose for your next symfony project, make sure that you put the productivity in the balance.