codenugget.org

Building multiple JOIN conditions with Propel's new query API

Or: How Propel got its love back ;)

I recently updated a software project I started in autumn 2008. It was developed using symfony 1.1 with Propel 1.2 as ORM. Back then, we were stucked with PHP 5.1.6 (blame RHEL for that) and I didn’t really had the chance to update to symfony 1.2 when the development cycle for 1.1 ended in summer 2009. This year we eventually had the chance to update to PHP 5.3 which broke my project due to a conflict with mysqlnd and Creole, Propel’s former database abstraction layer. So, I had no more excuses for not updating to a more recent version of symfony.

I also thought about trashing Propel in favor of Doctrine, because Propel 1.2 was slow, buggy and the Criteria API for building database queries was a real PIA. The downside of switching was that it would have taken me weeks to replace every piece of Propel code with Doctrine, so I tried to postpone this decision as good as I could ;)

Meanwhile, François Zaninotto took the lead for the (almost dead) Propel project and brought it back to life. He recently released version 1.5, which feels like a christmas present for all developers that have to deal with Propel legacy code. It features a whole new API for building queries. It can’t be denied that this API resembles Doctrine’s DQL, but it’s really well done, elegant (maybe a bit more elegant than DQL) and blazingly fast. But the best aspect, for my part, is that version 1.5 is fully backward compatible to older versions of Propel. The new query API was build on top of the old Criteria API so it’s up to you to use the old style, the new one, or a mix of both.

After playing around for a while, I was really getting into the new query API. But there was one thing I couldn’t figure out. With Doctrine, it’s really easy to build a query with a complex join condition like this:

$q = Doctrine_Query::create()       
  ->from('Book b')       
  ->leftJoin('b.Author a WITH a.name = ?', 'Douglas Adams')       
  ->where('b.title LIKE ?', '%Hitchhiker%');            

$bookList = $q->find();

which produces the following (or a similar) raw MySQL query:

SELECT b.*     
FROM books b     
LEFT JOIN authors a ON a.id = b.author_id AND a.name = 'Douglas Adams'     
WHERE b.title LIKE '%Hitchhiker%'

How can this be archieved with Propel’s new query API?

$q = BookQuery::create()       
  ->leftJoin( /* what goes here?!? */ )       
  ->where('Book.Title LIKE ?', '%Hitchhiker%');            

$bookList = $q->find();

There’s no such thing as the WITH keyword in DQL. Since the relation itself is resolved automatically by Propel, there’s no chance to add a condition inside the leftJoin method. So, here’s what you will end up with:

$q = BookQuery::create()       
  ->leftJoin('Book.Author')       
  ->where('Book.Title LIKE ?', '%Hitchhiker%');            

$bookList = $q->find();

But that’s not what we want. After spending hours with reading through the documentation (but not finding a single clue), I gave a shout to the official Propel Google user group and kindly received a response by François himself.

Guess what: Model Query classes extend Criteria, so you can use
addMultipleJoin() on a query object exactly the same way as you do it with
Criteria. The same goes for every Criteria method.

Cheers,
François

Of course, since Propel 1.4 there was a way to add complex / multiple join conditions within a Criteria object

$c = new Criteria();      
$c->addMultipleJoin(array(          
    array(BookPeer::AUTHOR_ID, AuthorPeer::ID),          
    array(AuthorPeer::NAME, 'Douglas Adams'))        
  Criteria::LEFT_JOIN);

and since a query object is a Criteria, both “syntaxes” can be mixed. But, sadly, that’s only half the truth when it comes to more complex queries. Consider the following situation: You’re writing a bulletin board software, where you have a users table and a postings table. When a user accesses a posting, it should be marked as read, but, obviously, it should be only marked as read for the specific user and not for everyone else. So we need a third table, called read_statuses where we will store a posting id, a user id and (maybe) a timestamp everytime a user accesses a new posting. What if we now wanted to display a list of unread postings for a certain user id? Our raw SQL query could look something like this:

SELECT p.*     
FROM postings p     
LEFT JOIN read_statuses rs ON rs.posting_id = p.id AND rs.user_id = 123    
WHERE rs.posting_id IS NULL

I tried the following approach first:

$q = PostingQuery::create()       
  ->addMultipleJoin(array(            
      array(ReadStatusPeer::POSTING_ID, PostingPeer::ID),           
      array(ReadStatusPeer::USER_ID, 123))          
    Criteria::LEFT_JOIN)       
  ->where('ReadStatus.PostingId IS NULL');

Although this query looks good, it won’t work because the Query object does not know about the ReadStatus alias used in the where condition. The downside in mixing both “syntaxes” is that you might be forced to stick with the old Criteria syntax even if you want to use the new API. Here’s the fix for the approach above

$q = PostingQuery::create()       
  ->addMultipleJoin(array(            
      array(ReadStatusPeer::POSTING_ID, PostingPeer::ID),           
      array(ReadStatusPeer::USER_ID, 123))          
    Criteria::LEFT_JOIN)       
  ->add(ReadStatusPeer::POSTING_ID);

It works fine but now it’s almost entirely solved with the old, verbose and hard to read Criteria API. It might not be a big deal for relatively simply queries like the one above, but, in my project, there were rather complex ones with lots of joins and where conditions. Eventually, I ended up writing almost pure Criteria code again. Bummer! So I got back to the “blackboard” and dug back into Propel’s and my code. And suddenly I found a solution that eventually made me a happy camper again. After adding a join condition to the query object, you can retrieve a corresponding join object from the query object and extend it for additional conditions. Here’s how:

$q = PostingQuery::create()       
  ->leftJoin('Posting.ReadStatus')       
  ->where('ReadStatus.PostingId IS NULL');           

$join = $q->getJoin('Posting.ReadStatus');   
$join->addCondition(ReadStatusPeer::USER_ID, 123, Criteria::LEFT_JOIN);

I really don’t know if François wanted those method calls to be exposed to the Propel users, cause it feels a little bit hacky, but it works fine for me, tidies up my code, makes it more readable and reduces the amount of Criteria code to an absolut minimum.

I hope this will help anyone seeking for a solution to a similar problem :)