Allow more than more than one criteria for addJoin()

[Slick Rick]

Take the following statement:

SELECT usergroup.ID, usergroup.NAME, usergroup.CODE, usergroup_user_group.USER_ID
FROM usergroup
LEFT JOIN usergroup_user_group ON (usergroup.ID=usergroup_user_group.USERGROUP_ID '''AND usergroup_user_group.USER_ID=1''')

Currently, as far as I can see, there's no way to add more than one criteria on a JOIN statement using AND.

Am I missing something or is Propel?

[Slick Rick]

Sorry I tried to use BOLD inside the code block and it didn't work, so ignore the triple single quotes in the SQL statement.

[hans]

We will be redesigning Criteria for Propel 2.0, so I will move this to that milestone so we will give it more consideration then. (Until then this would probably be a case for custom SQL).

[anonymous]

Found and coded solution that is backward compatible with Propel 1.3 and the solution is about 5 lines.

Anyone interested in? I would like to contribute it.

Can I register as Propel developer?

Thanks.

[anonymous]

Previous post:

my e-mail jan.cinert{at}elevator.cz

sorry...

[jan]

Solution:

• add method addMultipleJoin to Criteria class

      public function addMultipleJoin($conditions, $operator = null) 
      {
      	$this->joins [] = new Join($conditions[0]['left'], $conditions[0]['right'], $operator, $conditions);
  
          return $this;
      }
  

left and right column from first of multiple conditions is assigned to leftColumn and rightColumn of Join, because BasePeer::createSelectSql() use them to detect whether ignore case...

• modify Join constructor and add method getMultipleConditions()

  public function __construct($leftColumn, $rightColumn, $joinType = null, $multipleConditions = array())
      {
  	    $this->leftColumn = $leftColumn;
  	    $this->rightColumn = $rightColumn;
  	    $this->joinType = $joinType;
  	    $this->multipleConditions = $multipleConditions;
      }
  
      public function getMultipleConditions()
      {
  	    return $this->multipleConditions;
      }
  

• replace code in BasePeer::createSelectSql() where join condition is built with

// build the condition
			$multipleConditions = $join->getMultipleConditions();
			if ($ignoreCase) {
				if( $multipleConditions ) {
					$condition = array();
					foreach ($multipleConditions as $multipleCondition) {
						$condition[] = $db->ignoreCase($multipleCondition['left']) . (empty( $multipleCondition['operator'] ) ? '=' : $multipleCondition['operator']) . $db->ignoreCase($multipleCondition['right']);
					}
					$condition = implode(' AND ', $condition);
				}
				else
					$condition = $db->ignoreCase($join->getLeftColumn()) . '=' . $db->ignoreCase($join->getRightColumn());
			}
			else {
				if($multipleConditions) {
					$condition = array();
					foreach ($multipleConditions as $multipleCondition) {
						$condition[] = $multipleCondition['left'] . (empty( $multipleCondition['operator'] ) ? '=' : $multipleCondition['operator']) . $multipleCondition['right'];
					}
					$condition = implode(' AND ', $condition);
				}
				else
					$condition = $join->getLeftColumn() . '=' . $join->getRightColumn();
			}

Criteria->addJoin( ... ) is backward compatible.

For each condition in Criteria->addMultipleJoin( ... ) you can specify an operator like Criteria::EQUAL, Criteria::LESS_EQUAL and so on.

No other changes are neccessary.

Example call:

$c->addMultipleJoin( array( 
		   array( 
		     'left'  =>   FoldersPeer::alias( 'fo', FoldersPeer::LFT),
		     'right' =>   FoldersPeer::alias( 'parent', FoldersPeer::LFT ),
		     'operator'=> Criteria::GREATER_EQUAL ),
		   array( 
		     'left'  =>   FoldersPeer::alias( 'fo', FoldersPeer::LFT ),
		     'right' =>   FoldersPeer::alias( 'parent', FoldersPeer::RGT ),
		     'operator'=> Criteria::LESS_EQUAL )
		   ),
		 Criteria::JOIN
 	   );

[anonymous]

Considering that the fix for this is pretty much done, would you consider moving this back to milestone 1.3? We'd like to see it implemented as soon as possible.

[hans]

I've also had use for this. I'd like to see it implemented too ;)

[Harm]

I've made a patch of the fixes above against the 1.3 branch.

It implements the changes made by Jan and adds hackish support for joins on 2 tables. (it does not allow '.' in the right condition because that will try to resolve it to a table to join on)

This allows you to construct the following (not very usefull) code:

$criteria->addMultipleJoin(array(
              array('left' => AuthorPeer::ID,
                    'right' => BookPeer::AUTHOR_ID,
                    'operator' => Criteria::EQUAL),
              array('left' => BookPeer::PUBLISHER_ID,
                    'right' => PublisherPeer::ID,
                    'operator' => Criteria::EQUAL),
              array('left' => PulisherPeer::NAME,
                    'right' => 'My publisher',
                    'operator' => Criteria::EQUAL)
              ),
              Criteria::LEFT_JOIN
);

Which results in the following SQL:

SELECT * FROM author
LEFT JOIN (book, publisher) ON (author.ID = book.AUTHOR_ID AND book.PUBLISHER_ID = publisher.ID
AND publisher.name = 'My publisher')

I needed this construct to create a single record export of a one-to-many relation. It adds a new select column for each record in the one part of the relation.

[Fabian.Lange@…]

Hi, we "hacked" this like this

$c->addJoin(EntityPeer::GID,WatchPeer::DESTINATION_GID.' AND '.WatchPeer::USER_ID.'='.$user,Criteria::LEFT_JOIN);

however this collides with the strrpos change detecting the period.

[hans]

I believe this has been fixed with #606

[andy@…]

This patch and the fix implemented for #606 are not quite the same - this patch is more general, allowing for different types of comparision operator for each join condition. The update made for #606 only enables using multiple join conditions that all use the 'equals' comparision.

So e.g. this patch allows

SELECT...
LEFT JOIN b ON (a.col = b.col AND a.col2 < b.col2 AND b.col3 IN (...))

which the fix for #606 doesnt support because of the different types of comparision used (<, IN)

[andy@…]

I've now got a patch to reimplement jan's addMultipleJoin() from comment 5 alongside the new updates made by #606 but can't upload it (rejected as spam). Let me know if this is helpful..perhaps I could get an account to help contribute?

[andy]

implement jan's addMultipleJoin() alongside updates from #606

[francois]

in r1121

[francois]

(In [1175]) [1.4] Refactored PeerBuilder to use the new join syntax (refs #167)