public function testAddExplicitConditionWithAlias() { $j = new Join(); $j->addExplicitCondition('a', 'foo', 'Alias', 'b', 'bar', 'Blias'); $this->assertEquals('=', $j->getOperator()); $this->assertEquals('Alias.foo', $j->getLeftColumn()); $this->assertEquals('Blias.bar', $j->getRightColumn()); $this->assertEquals('a', $j->getLeftTableName()); $this->assertEquals('b', $j->getRightTableName()); $this->assertEquals('Alias', $j->getLeftTableAlias()); $this->assertEquals('Blias', $j->getRightTableAlias()); }
public function testJoinObject() { $j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_2'); $this->assertEquals('INNER JOIN', $j->getJoinType()); $this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn()); $this->assertEquals('TABLE_A', $j->getLeftTableName()); $this->assertEquals('COL_1', $j->getLeftColumnName()); $this->assertEquals('TABLE_B.COL_2', $j->getRightColumn()); $this->assertEquals('TABLE_B', $j->getRightTableName()); $this->assertEquals('COL_2', $j->getRightColumnName()); $j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_1', Criteria::LEFT_JOIN); $this->assertEquals('LEFT JOIN', $j->getJoinType()); $this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn()); $this->assertEquals('TABLE_B.COL_1', $j->getRightColumn()); $j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_1', Criteria::RIGHT_JOIN); $this->assertEquals('RIGHT JOIN', $j->getJoinType()); $this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn()); $this->assertEquals('TABLE_B.COL_1', $j->getRightColumn()); $j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_1', Criteria::INNER_JOIN); $this->assertEquals('INNER JOIN', $j->getJoinType()); $this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn()); $this->assertEquals('TABLE_B.COL_1', $j->getRightColumn()); $j = new Join(array('TABLE_A.COL_1', 'TABLE_A.COL_2'), array('TABLE_B.COL_1', 'TABLE_B.COL_2'), Criteria::INNER_JOIN); $this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn(0)); $this->assertEquals('TABLE_A.COL_2', $j->getLeftColumn(1)); $this->assertEquals('TABLE_B.COL_1', $j->getRightColumn(0)); $this->assertEquals('TABLE_B.COL_2', $j->getRightColumn(1)); }
/** * Add a join with multiple conditions * @deprecated use Join::setJoinCondition($criterion) instead * * @see http://propel.phpdb.org/trac/ticket/167, http://propel.phpdb.org/trac/ticket/606 * * Example usage: * $c->addMultipleJoin(array( * array(LeftPeer::LEFT_COLUMN, RightPeer::RIGHT_COLUMN), // if no third argument, defaults to Criteria::EQUAL * array(FoldersPeer::alias( 'fo', FoldersPeer::LFT ), FoldersPeer::alias( 'parent', FoldersPeer::RGT ), Criteria::LESS_EQUAL ) * ), * Criteria::LEFT_JOIN * ); * * @see addJoin() * @param array $conditions An array of conditions, each condition being an array (left, right, operator) * @param string $joinType A String with the join operator. Defaults to an implicit join. * * @return Criteria A modified Criteria object. */ public function addMultipleJoin($conditions, $joinType = null) { $join = new Join(); $joinCondition = null; foreach ($conditions as $condition) { $left = $condition[0]; $right = $condition[1]; if ($pos = strrpos($left, '.')) { $leftTableAlias = substr($left, 0, $pos); $leftColumnName = substr($left, $pos + 1); list($leftTableName, $leftTableAlias) = $this->getTableNameAndAlias($leftTableAlias); } else { list($leftTableName, $leftTableAlias) = array(null, null); $leftColumnName = $left; } if ($pos = strrpos($right, '.')) { $rightTableAlias = substr($right, 0, $pos); $rightColumnName = substr($right, $pos + 1); list($rightTableName, $rightTableAlias) = $this->getTableNameAndAlias($rightTableAlias); } else { list($rightTableName, $rightTableAlias) = array(null, null); $rightColumnName = $right; } if (!$join->getRightTableName()) { $join->setRightTableName($rightTableName); } if (!$join->getRightTableAlias()) { $join->setRightTableAlias($rightTableAlias); } $conditionClause = $leftTableAlias ? $leftTableAlias . '.' : ($leftTableName ? $leftTableName . '.' : ''); $conditionClause .= $leftColumnName; $conditionClause .= isset($condition[2]) ? $condition[2] : JOIN::EQUAL; $conditionClause .= $rightTableAlias ? $rightTableAlias . '.' : ($rightTableName ? $rightTableName . '.' : ''); $conditionClause .= $rightColumnName; $criterion = $this->getNewCriterion($leftTableName . '.' . $leftColumnName, $conditionClause, Criteria::CUSTOM); if (null === $joinCondition) { $joinCondition = $criterion; } else { $joinCondition = $joinCondition->addAnd($criterion); } } $join->setJoinType($joinType); $join->setJoinCondition($joinCondition); return $this->addJoinObject($join); }
/** * Executes query build by createSelectSql() and returns ResultSet. * * @param Criteria $criteria A Criteria. * @param Connection $con A connection to use. * @return ResultSet The resultset. * @throws PropelException * @see createSelectSql() */ public static function doSelect(Criteria $criteria, $con = null) { $arrTables = array_keys($criteria->getTablesColumns()); if (sizeof($arrTables) > 1) { $arrJoins = array(); foreach ($criteria->getJoins() as $objJoin) { if (false) { $objJoin = new Join(); } $arrJoins[] = $objJoin->getLeftTableName(); $arrJoins[] = $objJoin->getRightTableName(); } array_unique($arrJoins); $arrMissedJoinsWhatever = array_diff($arrTables, $arrJoins); } // endif $dbMap = Propel::getDatabaseMap($criteria->getDbName()); if ($con === null) { $con = Propel::getConnection($criteria->getDbName()); } $stmt = null; try { // Transaction support exists for (only?) Postgres, which must // have SELECT statements that include bytea columns wrapped w/ // transactions. if ($criteria->isUseTransaction()) { $con->begin(); } $params = array(); $sql = self::createSelectSql($criteria, $params); $stmt = $con->prepareStatement($sql); $stmt->setLimit($criteria->getLimit()); $stmt->setOffset($criteria->getOffset()); self::populateStmtValues($stmt, $params, $dbMap); $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); if ($criteria->isUseTransaction()) { $con->commit(); } } catch (Exception $e) { if ($stmt) { $stmt->close(); } if ($criteria->isUseTransaction()) { $con->rollback(); } Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException($e); } if (isset($arrMissedJoinsWhatever) && sizeof($arrMissedJoinsWhatever)) { $strBody = "The probable error, a lack of joins tables " . var_export($arrMissedJoinsWhatever, true) . " in query: \n\n" . $sql; // $strBody .= "\n\n".sfContext::getInstance()->getRequest()->getUri(); throw new Exception($strBody); } // endif return $rs; }