Blog: The Ultimate ORM Query API. Only With Propel.

The Propel Team – 06 January 2010

This post is pretty long, but it explains a new shift in the Propel Query API, and shows how this new syntax will be the killer feature of Propel 1.5. You’ll soon learn more about it, but first, let’s see is how the idea came.

Better Is Not Best

The latest additions in the Propel Query API intended to make it easier to read and write queries. For one part, an important factor of readability is the amount of code necessary to write a query. From this point of view, the recent ModelCriteria syntax, introduced in the 1.5 branch, appears as an important improvement:

// Propel 1.4 way
$c = new Criteria();
$c->add(BookPeer::PRICE, 40, Criteria::LESS_THAN);
$c->addAscendingOrderByColumn(BookPeer::TITLE);
$book = BookPeer::doSelectOne($c);
 
// Propel 1.5 way
$book = PropelQuery::from('Book')
  ->where('Book.Price < ?', 40)
  ->orderBy('Book.Title')
  ->findOne();

But after introducing this new syntax in a recent blog post, I received negative reactions from various Propel users who complained about the lost ability to use autocompletion in an IDE.

At first, I saw these complaints as reluctance to change. I thought that people would get used to the new syntax pretty quickly. They would also see how the added benefits of ModelCriteria compensate the lost IDE autocompletion.

IDE Autocompletion Is A Key Feature

And then I tried it myself, with an IDE. I have to admit that autocompletion helps a lot. In fact, using NetBeans, the Propel 1.5 syntax requires a little longer to write than the Propel 1.4 syntax. Besides, the IDE provides the comfort and insurance that the code was correct before running it, while any typo in the 1.5 code can only appear at runtime.

That didn’t decide me to use an IDE for day-to-day coding - I’m satisfied with the reactivity of Textmate and I don’t like the blinking interfaces of IDEs. But that opened my eyes on a key feature that Propel has, and that no other ORM offers. The ability to use autocompletion is one big advantage of Propel that can’t be left aside, or else Propel will lose part of its IDE-users community.

That didn’t decide me to drop the Propel 1.5 syntax either, because after years of using Propel, I can’t read a Criteria query without whispering, and I can’t write one without complaining. The fast syntax offered by other ORMs makes Propel look like C. The Propel 1.4 syntax is still counterintuitive, and the fact that it relies on class constants makes a lot of thinks impossible.

That means that the ModelCriteria syntax is just a step in the path of The Definitive Query Syntax. But then how to improve it?

Code Generation To The Rescue

ModelCriteria uses runtime introspection to offer very powerful features. And it also uses code generation to support query behaviors. In fact, any call to PropelQuery::from('Book') returns an instance of BookQuery, which is a class generated by Propel alongside Book and BookPeer.

But the PropelQuery::from() factory has no way to tell an IDE which class it is going to return. The phpDoc for this method stipulates that the return value is an instance of ModelCriteria, but in reality it’s a subclass of it.

So the first step is to improve the generated BookQuery to give it the charge of the factory. By generating a BookQuery::create() method, returning a new instance of BookQuery, it becomes possible to tell the IDE which class is actually in use:

 // Old Propel 1.5 way
 $book = PropelQuery::from('Book')
   ->where('Book.Price < ?', 40)
   ->orderBy('Book.Title')
   ->findOne();
 
// New Propel 1.5 way
 $book = BookQuery::create()
   ->where('Book.Price < ?', 40)
   ->orderBy('Book.Title')
   ->findOne();

Generated Filter Methods

But the generated query objects, like BookQuery, have very few methods. And there is no way an IDE will help the developer to write the content of a string, as in the where() argument. So it’s necessary to modify the generator of the custom Query objects again, in order to add one where() method for every column of the table. And since the operator cannot be part of the method name, it has to move as second argument of the call. The example query then becomes:

class BookQuery extends BaseBookQuery
{
  // this method is generated

  /**
   * Filter the query on the price column
   * 
   * @param     mixed $price The value to use as filter.
   * @param     string $operator Operator to use for the comparison
   *
   * @return    BookQuery The current query, for fluid interface
   */
  public function wherePrice($price, $operator = Criteria::EQUAL)
  {
    return $this->whereColumn('Price', $price, $operator);
  }
}

// New Propel 1.5 way
$book = BookQuery::create()
   ->wherePrice(40, '<=')
   ->orderBy('Book.Price')
   ->findOne();

Notice how the phpDoc of the generated wherePrice() method stipulates that the method returns a BookQuery and not only a ModelCriteria. This is important to allow IDEs to keep on suggesting the correct methods when the fluid interface is used.

After seing the wherePrice() method call, you probably agree that it is not very good looking ; the operator without the column name is a call for a return to the Criteria constants. Maybe there is an alternative solution. Since the wherePrice() method is generated, the generator can use the fact that the price column is numeric to offer additional abilities. After further thinking, each column type should offer special comparison types : a string column should support wildcards, a boolean column should support string booleans (like ‘no’ or ‘false’), etc.

So after a new addition to the query generator, here is how the query looks:

class BookQuery extends BaseBookQuery
{
  // this method is generated
 
  /**
   * Filter the query on the price column
   * 
   * @param     double|array $price The value to use as filter.
   *            Accepts an associative array('min' => $minValue, 'max' => $maxValue)
   *
   * @return    BookQuery The current query, for fluid interface
   */
  public function wherePrice($price = null)
  {
    if (is_array($price)) {
      if (isset($price['min'])) {
        $this->whereColumn('Price', $price['min'], Criteria::GREATER_EQUAL);
      }
      if (isset($price['max'])) {
        $this->whereColumn('Price', $price['max'], Criteria::LESS_EQUAL);
      }
    } else {
      return $this->whereColumn('Price', $price, Criteria::EQUAL);
    }
  }
}

// New Propel 1.5 way
$book = BookQuery::create()
   ->wherePrice(array('max' => 40))
   ->orderBy('Book.Title')
   ->findOne();

The final touch is to rename these whereXXX() methods into filterByXXX() (and to rename whereColumn() to filterBy()). It’s not just a matter of taste, it’s a similarity with orderBy() and groupBy(), and a deliberate step away from relational SQL. More on that matter later, but in the meantime, here is the query again:

class BookQuery extends BaseBookQuery
{
  // this method is generated

  /**
   * Filter the query on the price column
   * 
   * @param     double|array $price The value to use as filter.
   *            Accepts an associative array('min' => $minValue, 'max' => $maxValue)
   *
   * @return    BookQuery The current query, for fluid interface
   */
  public function filterByPrice($price = null)
  {
    if (is_array($price)) {
      if (isset($price['min'])) {
        $this->filterBy('Price', $price['min'], Criteria::GREATER_EQUAL);
      }
      if (isset($price['max'])) {
        $this->filterBy('Price', $price['max'], Criteria::LESS_EQUAL);
      }
    } else {
      return $this->filterBy('Price', $price, Criteria::EQUAL);
    }
  }
}

// New Propel 1.5 way
$book = BookQuery::create()
   ->filterByPrice(array('max' => 40))
   ->orderBy('Book.Price')
   ->findOne();

phpDoc For Magic Methods

As for allowing the orderBy() method to use IDE completion, using generated methods is out of the question. It is acceptable to add filter methods that react differently according to the column type, it is not acceptable to add one-line proxy methods for each column just to support an orderByTitle() syntax.<p /> The ModelCriteria already supports a call to orderByTitle(), through the magic __call() method. But magic and IDE completion don’t get along well with each other. Or do they?

The phpDocumentor grammar allows one to document magic methods by way of @method comments in the class description, that the IDEs recognize. So the alternative to generating one method for every column is to generate one comment line for every column. That way the IDE sees the orderByTitle() method at development time, but this method doesn’t bloat the BookQuery code since __call() already handles it pretty well.

So the query can be even more ‘IDEified’ as follows:

/**
 * Base class that represents a query for the 'book' table.
 *
 * Book Table
 *
 * @method     BookQuery orderById($order = Criteria::ASC) Order by the id column
 * @method     BookQuery orderByTitle($order = Criteria::ASC) Order by the title column
 * @method     BookQuery orderByISBN($order = Criteria::ASC) Order by the isbn column
 * @method     BookQuery orderByPrice($order = Criteria::ASC) Order by the price column
 * @method     BookQuery orderByPublisherId($order = Criteria::ASC) Order by the publisher_id column
 * @method     BookQuery orderByAuthorId($order = Criteria::ASC) Order by the author_id column
 */
class BookQuery extends BaseBookQuery
{
   ...
}
 
// New Propel 1.5 way
$book = BookQuery::create()
   ->filterByPrice(array('min' => 40))
   ->orderByTitle()
   ->findOne();

The same addition is required for groupByXXX() methods.

And while adding smart phpDoc comments to the query class description, why not override the phpDoc for findOne()? That way an IDE knows that the returned $book is not just a Propel BaseObject, it’s a Book.

How About Relationships?

So far, so good, but how about the handling of conditions on related tables? For instance, how to translate this Propel 1.4 query into the new syntax?

// Find first book published at 'Penguin' editions
// Propel 1.4 way
$c = new Criteria();
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID);
$c->add(PublisherPeer::NAME, 'Penguin');
$book = BookPeer::doSelectOne($c);

This is in fact quite easy, since a recent addition to the ModelCriteria class allows it to use a secondary query object instead of applying conditions on a related column. So without any change in the Query generator, this can be written as follows:

// New Propel 1.5 way
$book = BookQuery::create()
  ->join('Book.Publisher')
  ->useQuery('Publisher')  // returns a new PublisherQuery instance
    ->filterByName('Penguin')
  ->endUse()       // returns the original BookQuery instance, merged with the PublisherQuery
  ->findOne();

But the two calls to join() and useQuery() are not very IDE friendly. No problem, let’s modify the query class generator again, so as to add support for a faster use() syntax:

class BookQuery extends BaseBookQuery
{
  // this method is generated

  /**
   * Use the Publisher relation Publisher object
   *
   * @return    PublisherQuery A secondary query class using the current class as primary query
   */
  public function usePublisherQuery()
  {
    return $this
      ->join($this->getModelAliasOrName() . '.Publisher')
      ->useQuery('Publisher', 'PublisherQuery');
  }
}

// New Propel 1.5 way
$book = BookQuery::create()
  ->usePublisherQuery()  // returns a new PublisherQuery instance and makes the join
    ->filterByName('Penguin')
  ->endUse()       // returns the original BookQuery instance, merged with the PublisherQuery
  ->findOne();

This is getting very interesting. Since this method is created at generation time, it’s possible to create useXXXQuery() for every foreign key, but also for the foreign keys of other tables pointing to Book.

Syntactic Sugar

And the subject of conditions on related tables is not closed. Propel still requires that you know foreign key columns to apply a condition on a related object. For instance:

// Propel 1.4 way
// $author is an Author object
$c = new Criteria();
$c->add(BookPeer::AUTHOR_ID, $author->getId());
$book = BookPeer::doSelectOne($c);

// new Propel 1.5 way
$book = BookQuery::create()
  ->filterByAuthorId($author->getId())
  ->findOne();

An ORM is all about thinking “objects” rather than “relational”, and yet this kind of query forces the developer to remember the relation between the columns of two tables. So in the process of adding generated methods to the Query classes, let’s make a tiny but very useful addition:

class BookQuery extends BaseBookQuery
{
   // this method is generated
 
  /**
   * Filter the query by a related Author object
   *
   * @param     Author $author the related object to use as filter
   * @return    BookQuery The current query, for fluid interface
   */
  public function filterByAuthor(Author $author)
  {
    return $this->filterBy('AuthorId', $author->getId());
  }
}

$book = BookQuery::create()
   ->filterByAuthor($author)
   ->findOne();

Wrapping It All Together

The generated query classes now have much more methods and phpDocumentation, and in fact everything is ready to provide a full IDE completion support. Let’s check on a final example:

// $author is an Author object
// Find all books by $author, sold for less than 40$, ordered by title, and published at 'Penguin' editions

// Version A: Propel 1.4 way
 $c = new Criteria();
$c->add(BookPeer::AUTHOR_ID, $author->getId());
$c->add(BookPeer::PRICE, 40, Criteria::LESS_THAN);
$c->addAscendingOrderByColumn(BookPeer::TITLE);
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID);
$c->add(PublisherPeer::NAME, 'Penguin');
$book = BookPeer::doSelectOne($c);

// Version B: Old Propel 1.5 way
$book = PropelQuery::from('Book')
  ->where('Book.AuthorId = ?', $author->getId())
  ->where('Book.Price < ?', 40)
  ->orderBy('Book.Title')
  ->join('Book.Publisher')
  ->where('Publisher.Name = ?', 'Penguin')
  ->findOne();

// Version C: New Propel 1.5 way
$book = BookQuery::create()
  ->filterByAuthor($author)
  ->filterByPrice(array('min' => 40))
  ->orderByTitle()
  ->usePublisherQuery()
     ->filterByName('Penguin')
  ->endUse()
  ->findOne();

Version C is both more concise than versions A and B, and totally compatible with IDE completion. It is still very readable, less error prone than version B, and extremely easy to extend.

The Meaning Of Everything

Incidentally, the new syntax cuts the link with the SQL query. The syntax doesn’t show any where() or join() calls. This is because the Propel Query API is now truly in the object oriented world.

Also, this new Propel Query syntax uses code generation the same way the Propel Models do. Code generation provides intuitive and IDE friendly methods. And these generated methods are very fast, because they don’t rely on string parsing or model introspection. Version C is faster to execute than version B.

Speed and code completion are the marks of Propel. No other ORM offer such good development tools that are also very performant at runtime. That’s the sign that this new syntax is the ultimate query syntax for Propel.

One last thing: if version C will be the default syntax for queries in Propel 1.5, versions A and B will also be supported. That means no backwards compatibility problem, and no switching problems for Doctrine users who might want to try the mighty Propel Query API.