Propel : Complex ON Clause

2-minute read
propel

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);  
        $this->setExtraCondition($extraCondition);  
    }

    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)";  
}

Usage

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;  

$c->addExtraJoin(  
     MoroAclRulePeer::ACL_RULE_ID,  
     MoroAclGroupPeer::ACL_RULE_ID,  
     Criteria::LEFT_JOIN,  
    ' AND '.MoroAclGroupPeer::GROUP_ID.'='.intval($this->getValue('GroupId')));  

MoroAclRulePeer::addSelectColumns($c);  
MoroAclGroupPeer::addSelectColumns($c);  

$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)

References