Boban Acimovic Consulting | Joining non-related tables in Propel 1.6

Boban Acimovic Consulting | Joining non-related tables in Propel 1.6

I had this problem few days ago and I couldn’t find any useful information on the Internet. I had to browse through Propel internals to find this out and it would be pity not to share it with others. So let’s start with an example:

$accounts = AccountQuery::create()
->joinWith('Account.AdditionalData')
->addJoin(AdditionalDataPeer::VAL, CountryPeer::ID)
->withColumn('country.name', 'CountryName')
->find();

Someone may ask why Country and Account objects have no relation. Well, this is just an example, but imagine that table AdditionalData contain many different values, so we can’t really make any foreign reference as it would have to reference many different tables, which is not possible. There may be other reasons to use something like this, but anyway let’s explain line by line how it works.

->joinWith('Account.AdditionalData')

This is just normal Propel join where we hydrate the main object with related object data.

->addJoin(AdditionalDataPeer::VAL, CountryPeer::ID)

This is the way how we can define additional join with non-related table. The first value, AdditionalDataPeer::VAL represents the field name in the left table and CountryPeer::Id it’s corresponding field in the right table. These constants are defined in base peer classes. As third parameter here you can define the type of join, but I have used just default (inner join). This works fine except the main object is not hydrated with the related object data. Unfortunately, this is not possible using with() method as with() works only with previous join(). Fortunately, there is another way using:

->withColumn('country.name', 'CountryName')

First parameter here is the real table name concatenated with real column name and the second one is an alias name for this column. You can include as many columns from the related table as you want. It’s probably possible to concatenate some predefined Propel constants for the first parameter, like CountryPeer::TABLE_NAME . ‘.’ . CountryPeer::ID, but I haven’t tried that.

And like the documentation for withColumn() says, you can use this value in the resulting object by method getVirtualColumn():

foreach ($accounts as $account) {
print $account->getVirtualColumn('CountryName') . PHP_EOL;
}

I hope this may help someone