One of the most time-consuming “busy work” tasks of developing a project always seems to be coding the data layer.  Using an ORM framework seems to help keep this work high-level and flexible (as well as giving you transactional support and other perks for free).  On one of my current PHP projects, I have been experimenting with using Propel as an ORM framework and have been very impressed so far.  It is relatively easy to set up and use (but don’t take my word for it; take a look for yourself).

I ran into a problem today that was deceptively easy to solve, although not so easy to find help with.  I had two foreign keys in my query that both referred back to the UserDirectory table and required multiple JOINs to the table.

It is farily easy to do this in plain SQL:

SELECT *
FROM unit u
INNER JOIN directoryUser dOwner ON Unit.UserId = dOwner. id
LEFT OUTER JOIN unitApproval ua ON u.id = ua.UnitId
LEFT OUTER JOIN directoryUser dByWhom ON ua.ByWhom = dByWhom.id
WHERE u.id = $_REQUEST["id"]

The real strength of Propel’s API is how dirt simple it is.  A basic join on a table is super easy, although what threw me off a bit is that it uses the PHP table name:

$u = UnitQuery::create()
->joinWith("DirectoryUser")
->findById($_REQUEST["id"]);

I was Googling up a storm.  I was digging around the ModelCriteria class.  All the time the answer was painfully simple and intuitive.

$u = UnitQuery::create()
->joinWith("DirectoryUser dOwner")
->leftJoinWith("UnitWeeks")
->leftJoinWith("UnitApproval")
->leftJoinWith("UnitApproval.DirectoryUser dByWhom")
->findById($_REQUEST["id"]);

Works like a champ!  Now that was easy!