Blog: Reduce Your Query Count With Propel 1.5

The Propel Team – 05 February 2010

Propel 1.5 offers a lot of new tools to help you reduce your query count. If you thought that the generated `doSelectJoin()` methods of Propel 1.2 were a great help, you're going to love Propel 1.5.

Instance Pool

But first, let's remind those of you who are not familiar with ORM terminology of one important notion: ''hydration''. Hydration is the process of populating a PHP object from a row in a database query result. In Propel, every generated model object offers a `hydrate()` method just for that purpose.

This hydration process can take some time, so Propel stores all the hydrated objects in an internal registry, called the ''instance pool''. If you try to hydrate the same row twice in a script, then Propel returns the object hydrated the first time, that it has kept in the instance pool.

Even better, if you need to retrieve a row using its primary key, and if an object was already hydrated from that row, Propel won't even execute the database query, but instead it will return the requested object from the instance pool. This is a precious time saver for queries like the following:

[code] $books = BookQuery::create()->find();     // one database query $authors = AuthorQuery::create()->find(); // one database query foreach    ($books as $book) {   echo $book->getAuthor()->getName();     // no query, since all the author rows are already hydrated } [/code]

Object Instance Pool has been in Propel since version 1.3. Chances are that you alredy benefit from it.

Collection Relation Population

But the previous example does not correspond to any real use case. In practice, you usually retrieve a subset of all the Authors and Books, and you can never be sure that the instance pool will contain the related object you need according to the query you make.

Fortunately, Propel offers a quick way to hydrate the objects related to a list of objects. This feature uses the fact that Propel queries return a `PropelCollection` object and not an array. That means that you can call methods on the results of a query. Starting with Propel 1.5, the `PropelObjectCollection` class offers a `populateRelation()` method that will change your life:

[code] $books = BookQuery::create()->find();     // one database query $books->populateRelation('Author');       // one database query foreach    ($books as $book) {   echo $book->getAuthor()->getName();     // no query, since the necessary author rows are already hydrated } [/code]

The difference from the previous example is that `populateRelation()` retrieves and hydrates only the `Author` objects related to the `Book` objects present in the `$books` collection. So `populateRelation()` needs a single effective query to reduce the query count.

And the greatest thing about `populateRelation()` is that it also works the other way around, that means for one-to-many relationships:

[code] $authors = AuthorQuery::create()->find(); // one database query $authors->populateRelation('Book');       // one database query foreach    ($authors as $author) {   foreach ($author->getBooks() as $book) { // no query, since the necessary book rows are already hydrated     echo $book->getTitle(), $author->getName();     // no query either   } } [/code]

No more scripts with a query count proportional to the number of results! `PropelObjectCollection::populateRelation()` will keep the query count reasonnable in all situations, in all database models.

Query With Class

There is one limitation, though. `populateRelation()` only allows to hydrate relations of the main object. You can't hydrate, for instance, relations of a relation. For that purpose, you will need to add one line in your Query, and it's called `joinWith()`.

`joinWith()` expects a composed relation name ('Start.End') and can be called several times in a query. It tells the query to also hydrate the related objects on a many-to-one relationship. That makes the following query possible:

[code] $books = BookQuery::create()   ->joinWith('Book.Author')   ->joinWith('Book.Publisher')   ->joinWith('Publisher.Group')   ->find(); // one database query foreach ($books as $book) {   echo $book->getAuthor()->getName();                // no query   echo $book->getPublisher()->getName();             // no query   echo $book->getPublisher()->getGroup()->getName(); // no query } [/code]

`joinWith()` doesn't work with one-to-many relationships, because it would require some very heavy code to handle the LIMIT clause in a query. And since `populateRelation()` deals with most of the use cases, Propel keeps its codebase lightweight and fast by not implementing `joinWith()` on one-to-many relationships.

Query With Column

Sometimes you don't need to hydrate a full object in addition to the main object. If you only need one additional column, the `withColumn()` method is a good alternative to `joinWith()`:

[code] $book = PropelQuery::from('Book')   ->join('Book.Author')   ->withColumn('Author.Name', 'AuthorName')   ->findOne(); $authorName = $book->getAuthorName(); [/code]

Propel adds the 'with' column to the SELECT clause of the query, and uses the second argument of the `withColumn()` call as a column alias. This additional column is later available as a 'virtual' column, i.e. using a getter that does not correspond to a real column. You don't actually need to write the `getAuthorName()` method ; Propel uses the magic `__call()` method of the generated `Book` class to catch the call to a virtual column.

`withColumn()` is also of great use to add calculated columns:

[code] $authors = PropelQuery::from('Author')   ->leftJoin('Author.Book')   ->withColumn('COUNT(Book.Id)', 'NbBooks')   ->groupBy('Author.Id')   ->find(); foreach ($authors as $author) {     echo $author->getName() . ': ' . $author->getNbBooks() . " books\n"; } [/code]

With a single SQL query, you can have both a list of objects and an additional column for each object. This makes `withColumn()` a great query saver.

Of course, you can call `withColumn()` multiple times to add more than one virtual column to the resulting objects.

Conclusion

Instance pooling, collection relation population, `joinWith()`, and `withColumn()` are the new weapons that Propel 1.5 gives you to fight for your application performance. You will find them very easy to use, and you will soon wonder how you could program without them in the past!