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

3 responses to “Propel: Joining to non-propel mapped tables – conquered

  1. Yes, this is an excellent tip.

    Note, if you are joining to a table that isn’t even in the same *database*, you have to do things a little differently:

    $j = new Join();
    $j->setJoinType(Criteria::LEFT_JOIN);
    $j->addExplicitCondition(‘DB.Table’,’Column’,’Alias’,’DB2.Table2′,’Column’,’Alias’)

    where DB.Table is the one propel knows about and DB2.Table2 is the one you are joining to — the built-in methods on Join all assume that you are sticking to the same database.

  2. Dave – You rock. I just searched for “propel addAsColumn from non propel table” and found this, exactly what I needed. You made my day!
    -Pete

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s