2006-09-16 - 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);
        $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

Comments

comments powered by Disqus