Propel : Complex ON Clause

In propel 1.2.0, there is no way to create a complex join with multiple select inside an ON clause. This article explains how to hack the propel code to make it works. Please, this solution is a quick and dirty hack and may not work on all database engines. However, this code has been tested on a MySQL database engine and it works just fine.

To do so, we need to change two files : Criteria.php and BasePeer.php.

Step 1 : Add Criteria::addExtraJoin method

public function addExtraJoin($left, $right, $operator = null, $extra = null)  
   $this->joins [] = new ExtraJoin($left, $right, $operator, $extra);  
   return $this;  

Step 2 : Add new Class ExtraJoin in Criteria.php

class ExtraJoin extends Join {  
    private $extraCondition = '';  

     * Constructor  
     * @param leftColumn the left column of the join condition;  
     *        might contain an alias name  
     * @param rightColumn the right column of the join condition  
     *        might contain an alias name  
     * @param joinType the type of the join. Valid join types are  
     *        null (adding the join condition to the where clause),  
     *        Criteria::LEFT_JOIN(), Criteria::RIGHT_JOIN(), and Criteria::INNER_JOIN()  
     * @param extraCondition allow to add more than one condition inside ON clause  
    public function __construct($leftColumn, $rightColumn, $joinType = null, $extraCondition = '')  
        parent::__construct($leftColumn, $rightColumn, $joinType);  

    public function setExtraCondition($condition) {  
        $this->extraCondition = $condition;  

    public function getExtraCondition() {  
        return $this->extraCondition;  

Step 3 : Change BasePeer::createSelectSql

// at the line 695.  

if($join instanceof ExtraJoin) {  
    $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . ' ON ('.$condition.' '.$join->getExtraCondition().')';  
} else {  
   $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . " ON ($condition)";  


Now to use the complex ON clause, you have to write a fourth parameter which is just a SQL section of the final query generated by Propel.

$c = new Criteria;  

    ' AND '.MoroAclGroupPeer::GROUP_ID.'='.intval($this->getValue('GroupId')));  


$rs = MoroAclRulePeer::doSelectRS($c);  

[...] Process the ResultSet [...]

The generated SQL for this criteria is

SELECT [...]  
FROM moro_acl_rules  
LEFT JOIN moro_acl_group  
  ON (moro_acl_rules.ACL_RULE_ID=moro_acl_group.ACL_RULE_ID  AND moro_acl_group.GROUP_ID=7)