Propel: Joining to non-propel mapped tables – conquered

For some time I’ve been avoiding figuring this out. Today I ‘faced my fear’ 🙂

I have a core project which uses Propel for DB access. This core project has an ‘extension’ architecture (plugins), and many of these extensions also benefit from using Propel.

I did not want to make the core project dependent on the extensions (obviously) – but have ended up with the extensions not being able to easily link with the Propel in the core. For example, a Print Job Management extension, the extension code has a PrintJobUser table (php name) with UserId column (php name) which is effectively a reference to the User table in the core project. Now because the extension has it’s own schema Propel doesn’t understand it’s a relationship.

In the past I’ve written simple code in the Propel derived classes to make it easy to jump back to the core Propel Objects. But in this case I simply needed the full collection of PrintJobUser objects rehydrated with ‘virtual columns’ for 3 fields from the core projects User table (oh – and a couple of PrintJob propel joins too).

So effectively what I’ve done here is joining Propel mapped tables with a non-Propel mapped table with the extra fields appearing as virtual columns.

The key functions are actually on Criteria rather than ModelCriteria – addJoinObject() and addAsColumn() – but using these directly messes up ModelCriteria slightly necessitating a call to addSelectColumn() to ensure the main class we’re trying to hydrate still has it’s fields there.

<?php
public function getAllUsers() {
        return PrintJobUserQuery::create()
            ->addSelectColumn('print_job_user.*')
            ->joinWith('PrintJobUser.PrintJobCompany')
            ->joinWith('PrintJobUser.PrintJobDepartment')
            ->addJoinObject(new Join("print_job_user.user_id", "user.userId",
                Criteria::LEFT_JOIN))
            ->addAsColumn('UserName', 'user.name')
            ->addAsColumn('UserLogin', 'user.login')
            ->addAsColumn('UserSecurityLevel', 'user.userSecurityLevel')
            ->find();
    }
?>

There are a couple of gotchas which are worth noting:

• The call to addSelectColumn() must come first and must use the MySQL table name.
  Calling addAsColumn() seems to set up the Criteria in such a way that when the select statement is built it no longer ‘automatically’ adds the columns for the class we’re querying, and hence we have to explicitly do it ourself. It has to come first or the fields don’t map properly during the rehydration process (at least that’s how it seemed to work for me?!?)
• The call to addJoinObject() uses the MySQL table names and field names
• The call to addJoinObject() must first have the class we’re building a query for, whether it’s left or right join, otherwise the FROM clause seems to come out wrong
• The calls to addAsColumn() must have the first parameter (the alias) is in the PHP form and will map directly to the getXXXXX calls to return these virtual columns on the hydrated class
• The calls to addAsColumn() must have the second parameter (the sql select element) in the MySQL table.column format as this just drops verbatim into the select statement