/** * Returns the "DELETE FROM <table> [AS <alias>]" part of DELETE query. * * @param Criteria $criteria * @param string $tableName * * @return string */ public function getDeleteFromClause($criteria, $tableName) { $sql = 'DELETE '; if ($queryComment = $criteria->getComment()) { $sql .= '/* ' . $queryComment . ' */ '; } if ($realTableName = $criteria->getTableForAlias($tableName)) { if ($this->useQuoteIdentifier()) { $realTableName = $this->quoteIdentifierTable($realTableName); } $sql .= $tableName . ' FROM ' . $realTableName . ' AS ' . $tableName; } else { if ($this->useQuoteIdentifier()) { $tableName = $this->quoteIdentifierTable($tableName); } $sql .= 'FROM ' . $tableName; } return $sql; }
/** * Init some properties with the help of outer class * @param Criteria $criteria The outer class */ public function init(Criteria $criteria) { //init $this->db try { $db = Propel::getDB($criteria->getDbName()); $this->setDB($db); } catch (Exception $e) { // we are only doing this to allow easier debugging, so // no need to throw up the exception, just make note of it. Propel::log("Could not get a DBAdapter, generated sql may be wrong", Propel::LOG_ERR); } //init $this->realtable $realtable = $criteria->getTableForAlias($this->table); if (!$realtable) { $realtable = $this->table; } $this->realtable = $realtable; }
/** * Method used to update rows in the DB. Rows are selected based * on selectCriteria and updated using values in updateValues. * <p> * Use this method for performing an update of the kind: * <p> * WHERE some_column = some value AND could_have_another_column = * another value AND so on. * * @param $selectCriteria A Criteria object containing values used in where * clause. * @param $updateValues A Criteria object containing values used in set * clause. * @param PropelPDO $con The PropelPDO connection object to use. * @return int The number of rows affected by last update statement. For most * uses there is only one update statement executed, so this number * will correspond to the number of rows affected by the call to this * method. Note that the return value does require that this information * is returned (supported) by the Propel db driver. * @throws PropelException */ public static function doUpdate(Criteria $selectCriteria, Criteria $updateValues, PropelPDO $con) { $db = Propel::getDB($selectCriteria->getDbName()); $dbMap = Propel::getDatabaseMap($selectCriteria->getDbName()); // Get list of required tables, containing all columns $tablesColumns = $selectCriteria->getTablesColumns(); if (empty($tablesColumns)) { $tablesColumns = array($selectCriteria->getPrimaryTableName() => array()); } // we also need the columns for the update SQL $updateTablesColumns = $updateValues->getTablesColumns(); $affectedRows = 0; // initialize this in case the next loop has no iterations. foreach ($tablesColumns as $tableName => $columns) { $whereClause = array(); $params = array(); $stmt = null; try { $sql = 'UPDATE '; if ($queryComment = $selectCriteria->getComment()) { $sql .= '/* ' . $queryComment . ' */ '; } // is it a table alias? if ($tableName2 = $selectCriteria->getTableForAlias($tableName)) { $udpateTable = $tableName2 . ' ' . $tableName; $tableName = $tableName2; } else { $udpateTable = $tableName; } if ($db->useQuoteIdentifier()) { $sql .= $db->quoteIdentifierTable($udpateTable); } else { $sql .= $udpateTable; } $sql .= " SET "; $p = 1; foreach ($updateTablesColumns[$tableName] as $col) { $updateColumnName = substr($col, strrpos($col, '.') + 1); // add identifiers for the actual database? if ($db->useQuoteIdentifier()) { $updateColumnName = $db->quoteIdentifier($updateColumnName); } if ($updateValues->getComparison($col) != Criteria::CUSTOM_EQUAL) { $sql .= $updateColumnName . '=:p' . $p++ . ', '; } else { $param = $updateValues->get($col); $sql .= $updateColumnName . ' = '; if (is_array($param)) { if (isset($param['raw'])) { $raw = $param['raw']; $rawcvt = ''; // parse the $params['raw'] for ? chars for ($r = 0, $len = strlen($raw); $r < $len; $r++) { if ($raw[$r] == '?') { $rawcvt .= ':p' . $p++; } else { $rawcvt .= $raw[$r]; } } $sql .= $rawcvt . ', '; } else { $sql .= ':p' . $p++ . ', '; } if (isset($param['value'])) { $updateValues->put($col, $param['value']); } } else { $updateValues->remove($col); $sql .= $param . ', '; } } } $params = self::buildParams($updateTablesColumns[$tableName], $updateValues); $sql = substr($sql, 0, -2); if (!empty($columns)) { foreach ($columns as $colName) { $sb = ""; $selectCriteria->getCriterion($colName)->appendPsTo($sb, $params); $whereClause[] = $sb; } $sql .= " WHERE " . implode(" AND ", $whereClause); } $stmt = $con->prepare($sql); // Replace ':p?' with the actual values self::populateStmtValues($stmt, $params, $dbMap, $db); $stmt->execute(); $affectedRows = $stmt->rowCount(); $stmt = null; // close } catch (Exception $e) { if ($stmt) { $stmt = null; } // close Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute UPDATE statement [%s]', $sql), $e); } } // foreach table in the criteria return $affectedRows; }
/** * Method to create select SQL. * * @param Criteria $criteria object used to create the SELECT statement. * @param String $tableName * @param Array &$params * @throws PropelException Any exceptions caught during processing will be * rethrown wrapped into a PropelException. */ private function createSelectSql($criteria, $tableName, &$params) { $db = Propel::getDB($criteria->getDbName()); // redundant definition $selectModifiers = array(); $selectClause = array(); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); $groupByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); $select = $criteria->getSelectColumns(); $aliases = $criteria->getAsColumns(); $fromClause[] = $criteria->getDBArrayTable(); // simple copy $selectModifiers = $criteria->getSelectModifiers(); // get selected columns foreach ($select as $columnName) { $tableName = null; $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price) $parenPos = strpos($columnName, '('); $dotPos = strpos($columnName, '.'); // [HL] I think we really only want to worry about adding stuff to // the fromClause if this function has a TABLE.COLUMN in it at all. // e.g. COUNT(*) should not need this treatment -- or there needs to // be special treatment for '*' if ($dotPos !== false) { if ($parenPos === false) { // table.column $tableName = substr($columnName, 0, $dotPos); } else { // FUNC(table.column) $tableName = substr($columnName, $parenPos + 1, $dotPos - ($parenPos + 1)); // functions may contain qualifiers so only take the last // word as the table name. // COUNT(DISTINCT books.price) $lastSpace = strpos($tableName, ' '); if ($lastSpace !== false) { // COUNT(DISTINCT books.price) $tableName = substr($tableName, $lastSpace + 1); } } $tableName2 = $criteria->getTableForAlias($tableName); if ($tableName2 !== null) { $fromClause[] = $tableName2 . ' ' . $tableName; } else { $fromClause[] = $tableName; } } // if $dotPost !== null } // set the aliases foreach ($aliases as $alias => $col) { $selectClause[] = $col . " AS " . $alias; } // add the criteria to WHERE clause foreach ($criteria->keys() as $key) { $criterion = $criteria->getCriterion($key); $someCriteria = $criterion->getAttachedCriterion(); $someCriteriaLength = count($someCriteria); $table = null; for ($i = 0; $i < $someCriteriaLength; $i++) { $tableName = $someCriteria[$i]->getTable(); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() || $someCriteria[$i]->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType() == "string"; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $cri['table'] = $criterion->table; $cri['field'] = $criterion->column; $cri['comparison'] = $criterion->comparison == '=' ? '==' : $criterion->comparison; $cri['value'] = $criterion->getValue(); $sb = ""; $sb .= "\$row['" . $cri['field'] . "'] " . $cri['comparison'] . "'" . $cri['value'] . "'"; $params[] = $cri; //$criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // Unique from clause elements $fromClause = array_unique($fromClause); if (!empty($orderBy)) { foreach ($orderBy as $orderByColumn) { // Add function expression as-is. if (strpos($orderByColumn, '(') !== false) { $orderByClause[] = $orderByColumn; continue; } // Split orderByColumn (i.e. "table.column DESC") $dotPos = strpos($orderByColumn, '.'); if ($dotPos !== false) { $tableName = substr($orderByColumn, 0, $dotPos); $columnName = substr($orderByColumn, $dotPos + 1); } else { $tableName = ''; $columnName = $orderByColumn; } $spacePos = strpos($columnName, ' '); if ($spacePos !== false) { $direction = substr($columnName, $spacePos); $columnName = substr($columnName, 0, $spacePos); } else { $direction = ''; } $orderByClause[] = array('columnName' => $columnName, 'direction' => $direction); } } // Build the SQL from the arrays we compiled $sql = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " FROM " . $fromClause[0] . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : ""); $dataSql['selectClause'] = $selectClause; $dataSql['fromClause'] = $fromClause; $dataSql['whereClause'] = $whereClause; $dataSql['orderByClause'] = $orderByClause; $dataSql['sql'] = $sql; return $dataSql; }
/** * Method to create an SQL query based on values in a Criteria. * * This method creates only prepared statement SQL (using ? where values * will go). The second parameter ($params) stores the values that need * to be set before the statement is executed. The reason we do it this way * is to let the Creole layer handle all escaping & value formatting. * * @param Criteria $criteria Criteria for the SELECT query. * @param array &$params Parameters that are to be replaced in prepared statement. * @return string * @throws PropelException Trouble creating the query string. */ public static function createSelectSql(Criteria $criteria, &$params) { $db = Propel::getDB($criteria->getDbName()); $dbMap = Propel::getDatabaseMap($criteria->getDbName()); // redundant definition $selectModifiers = array(); $selectClause = array(); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); // redundant definition $groupByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); $select = $criteria->getSelectColumns(); $aliases = $criteria->getAsColumns(); // simple copy $selectModifiers = $criteria->getSelectModifiers(); // get selected columns foreach ($select as $columnName) { // expect every column to be of "table.column" formation // it could be a function: e.g. MAX(books.price) $tableName = null; $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price) $parenPos = strpos($columnName, '('); $dotPos = strpos($columnName, '.'); // [HL] I think we really only want to worry about adding stuff to // the fromClause if this function has a TABLE.COLUMN in it at all. // e.g. COUNT(*) should not need this treatment -- or there needs to // be special treatment for '*' if ($dotPos !== false) { if ($parenPos === false) { // table.column $tableName = substr($columnName, 0, $dotPos); } else { // FUNC(table.column) $tableName = substr($columnName, $parenPos + 1, $dotPos - ($parenPos + 1)); // functions may contain qualifiers so only take the last // word as the table name. // COUNT(DISTINCT books.price) $lastSpace = strpos($tableName, ' '); if ($lastSpace !== false) { // COUNT(DISTINCT books.price) $tableName = substr($tableName, $lastSpace + 1); } } $tableName2 = $criteria->getTableForAlias($tableName); if ($tableName2 !== null) { $fromClause[] = $tableName2 . ' ' . $tableName; } else { $fromClause[] = $tableName; } } // if $dotPost !== null } // set the aliases foreach ($aliases as $alias => $col) { $selectClause[] = $col . " AS " . $alias; } // add the criteria to WHERE clause // this will also add the table names to the FROM clause if they are not already // invluded via a LEFT JOIN foreach ($criteria->keys() as $key) { $criterion = $criteria->getCriterion($key); $someCriteria = $criterion->getAttachedCriterion(); $someCriteriaLength = count($someCriteria); $table = null; for ($i = 0; $i < $someCriteriaLength; $i++) { $tableName = $someCriteria[$i]->getTable(); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() || $someCriteria[$i]->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType() == "string"; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $sb = ""; $criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // handle RIGHT (straight) joins // Loop through the joins, // joins with a null join type will be added to the FROM clause and the condition added to the WHERE clause. // joins of a specified type: the LEFT side will be added to the fromClause and the RIGHT to the joinClause // New Code. foreach ((array) $criteria->getJoins() as $join) { // we'll only loop if there's actually something here // The join might have been established using an alias name $leftTable = $join->getLeftTableName(); $leftTableAlias = ''; if ($realTable = $criteria->getTableForAlias($leftTable)) { $leftTableAlias = " {$leftTable}"; $leftTable = $realTable; } $rightTable = $join->getRightTableName(); $rightTableAlias = ''; if ($realTable = $criteria->getTableForAlias($rightTable)) { $rightTableAlias = " {$rightTable}"; $rightTable = $realTable; } // determine if casing is relevant. if ($ignoreCase = $criteria->isIgnoreCase()) { $leftColType = $dbMap->getTable($leftTable)->getColumn($join->getLeftColumnName())->getType(); $rightColType = $dbMap->getTable($rightTable)->getColumn($join->getRightColumnName())->getType(); $ignoreCase = $leftColType == 'string' || $rightColType == 'string'; } // build the condition if ($ignoreCase) { $condition = $db->ignoreCase($join->getLeftColumn()) . '=' . $db->ignoreCase($join->getRightColumn()); } else { $condition = $join->getLeftColumn() . '=' . $join->getRightColumn(); } // add 'em to the queues.. if ($joinType = $join->getJoinType()) { if (!$fromClause) { $fromClause[] = $leftTable . $leftTableAlias; } $joinTables[] = $rightTable . $rightTableAlias; $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . " ON ({$condition})"; } else { $fromClause[] = $leftTable . $leftTableAlias; $fromClause[] = $rightTable . $rightTableAlias; $whereClause[] = $condition; } } // Unique from clause elements $fromClause = array_unique($fromClause); // tables should not exist in both the from and join clauses if ($joinTables && $fromClause) { foreach ($fromClause as $fi => $ftable) { if (in_array($ftable, $joinTables)) { unset($fromClause[$fi]); } } } /* // Old Code. $joins =& $criteria->getJoins(); if (!empty($joins)) { for ($i=0, $joinSize=count($joins); $i < $joinSize; $i++) { $join =& $joins[$i]; $join1 = $join->getLeftColumn(); $join2 = $join->getRightColumn(); $tableName = substr($join1, 0, strpos($join1, '.')); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; } $dot = strpos($join2, '.'); $tableName = substr($join2, 0, $dot); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() && ($dbMap->getTable($table)->getColumn(substr($join2, $dot + 1))->getType() == "string")); if ($ignoreCase) { $whereClause[] = $db->ignoreCase($join1) . '=' . $db->ignoreCase($join2); } else { $whereClause[] = $join1 . '=' . $join2; } if ($join->getJoinType()) { $leftTable = $fromClause[count($fromClause) - 2]; $rightTable = $fromClause[count($fromClause) - 1]; $onClause = $whereClause[count($whereClause) - 1]; unset($whereClause[count($whereClause) - 1]); $fromClause [] = $leftTable . ' ' . $join->getJoinType() . ' ' . $rightTable . ' ON ' . $onClause; // remove all references to joinTables made by selectColumns, criteriaColumns for ($i = 0, $fromClauseSize=count($fromClause); $i < $fromClauseSize; $i++) { if ($fromClause[$i] == $leftTable || $fromClause[$i] == $rightTable) { unset($fromClause[$i]); } } } // If join type } // Join for loop } // If Joins */ // Add the GROUP BY columns $groupByClause = $groupBy; $having = $criteria->getHaving(); $havingString = null; if ($having !== null) { $sb = ""; $having->appendPsTo($sb, $params); $havingString = $sb; } if (!empty($orderBy)) { foreach ($orderBy as $orderByColumn) { // Add function expression as-is. if (strpos($orderByColumn, '(') !== false) { $orderByClause[] = $orderByColumn; continue; } // Split orderByColumn (i.e. "table.column DESC") $dotPos = strpos($orderByColumn, '.'); if ($dotPos !== false) { $tableName = substr($orderByColumn, 0, $dotPos); $columnName = substr($orderByColumn, $dotPos + 1); } else { $tableName = ''; $columnName = $orderByColumn; } $spacePos = strpos($columnName, ' '); if ($spacePos !== false) { $direction = substr($columnName, $spacePos); $columnName = substr($columnName, 0, $spacePos); } else { $direction = ''; } $tableAlias = $tableName; if ($aliasTableName = $criteria->getTableForAlias($tableName)) { $tableName = $aliasTableName; } $columnAlias = $columnName; if ($asColumnName = $criteria->getColumnForAs($columnName)) { $columnName = $asColumnName; } $column = $tableName ? $dbMap->getTable($tableName)->getColumn($columnName) : null; if ($column && $column->getType() == 'string') { $orderByClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}") . $direction; $selectClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}"); } else { $orderByClause[] = $orderByColumn; } } } // Build the SQL from the arrays we compiled $sql = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " FROM " . (!empty($joinClause) && count($fromClause) > 1 && substr(get_class($db), 0, 7) == 'DBMySQL' ? "(" . implode(", ", $fromClause) . ")" : implode(", ", $fromClause)) . ($joinClause ? ' ' . implode(' ', $joinClause) : '') . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : "") . ($havingString ? " HAVING " . $havingString : "") . ($orderByClause ? " ORDER BY " . implode(",", $orderByClause) : ""); Propel::log($sql . ' [LIMIT: ' . $criteria->getLimit() . ', OFFSET: ' . $criteria->getOffset() . ']', Propel::LOG_DEBUG); return $sql; }
/** * Method to create an SQL query based on values in a Criteria. * * This method creates only prepared statement SQL (using ? where values * will go). The second parameter ($params) stores the values that need * to be set before the statement is executed. The reason we do it this way * is to let the PDO layer handle all escaping & value formatting. * * @param Criteria $criteria Criteria for the SELECT query. * @param array &$params Parameters that are to be replaced in prepared statement. * @return string * @throws PropelException Trouble creating the query string. */ public static function createSelectSql(Criteria $criteria, &$params) { $db = Propel::getDB($criteria->getDbName()); $dbMap = Propel::getDatabaseMap($criteria->getDbName()); // redundant definition $selectModifiers = array(); $selectClause = array(); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); // redundant definition $groupByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); $select = $criteria->getSelectColumns(); $aliases = $criteria->getAsColumns(); // simple copy $selectModifiers = $criteria->getSelectModifiers(); // get selected columns foreach ($select as $columnName) { // expect every column to be of "table.column" formation // it could be a function: e.g. MAX(books.price) $tableName = null; $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price) $parenPos = strrpos($columnName, '('); $dotPos = strrpos($columnName, '.', $parenPos !== false ? $parenPos : 0); // [HL] I think we really only want to worry about adding stuff to // the fromClause if this function has a TABLE.COLUMN in it at all. // e.g. COUNT(*) should not need this treatment -- or there needs to // be special treatment for '*' if ($dotPos !== false) { if ($parenPos === false) { // table.column $tableName = substr($columnName, 0, $dotPos); } else { // FUNC(table.column) $tableName = substr($columnName, $parenPos + 1, $dotPos - ($parenPos + 1)); // functions may contain qualifiers so only take the last // word as the table name. // COUNT(DISTINCT books.price) $lastSpace = strpos($tableName, ' '); if ($lastSpace !== false) { // COUNT(DISTINCT books.price) $tableName = substr($tableName, $lastSpace + 1); } } $tableName2 = $criteria->getTableForAlias($tableName); if ($tableName2 !== null) { $fromClause[] = $tableName2 . ' ' . $tableName; } else { $fromClause[] = $tableName; } } // if $dotPost !== null } // set the aliases foreach ($aliases as $alias => $col) { $selectClause[] = $col . " AS " . $alias; } // add the criteria to WHERE clause // this will also add the table names to the FROM clause if they are not already // invluded via a LEFT JOIN foreach ($criteria->keys() as $key) { $criterion = $criteria->getCriterion($key); $someCriteria = $criterion->getAttachedCriterion(); $someCriteriaLength = count($someCriteria); $table = null; for ($i = 0; $i < $someCriteriaLength; $i++) { $tableName = $someCriteria[$i]->getTable(); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() || $someCriteria[$i]->isIgnoreCase()) && strpos($dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType(), "VARCHAR") !== false; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $sb = ""; $criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // Handle joins // joins with a null join type will be added to the FROM clause and the condition added to the WHERE clause. // joins of a specified type: the LEFT side will be added to the fromClause and the RIGHT to the joinClause foreach ((array) $criteria->getJoins() as $join) { // The join might have been established using an alias name $leftTable = $join->getLeftTableName(); $leftTableAlias = ''; if ($realTable = $criteria->getTableForAlias($leftTable)) { $leftTableAlias = " {$leftTable}"; $leftTable = $realTable; } $rightTable = $join->getRightTableName(); $rightTableAlias = ''; if ($realTable = $criteria->getTableForAlias($rightTable)) { $rightTableAlias = " {$rightTable}"; $rightTable = $realTable; } // determine if casing is relevant. if ($ignoreCase = $criteria->isIgnoreCase()) { $leftColType = $dbMap->getTable($leftTable)->getColumn($join->getLeftColumnName())->getType(); $rightColType = $dbMap->getTable($rightTable)->getColumn($join->getRightColumnName())->getType(); $ignoreCase = $leftColType == 'string' || $rightColType == 'string'; } // build the condition $condition = ''; foreach ($join->getConditions() as $index => $conditionDesc) { if ($ignoreCase) { $condition .= $db->ignoreCase($conditionDesc['left']) . $conditionDesc['operator'] . $db->ignoreCase($conditionDesc['right']); } else { $condition .= implode($conditionDesc); } if ($index + 1 < $join->countConditions()) { $condition .= ' AND '; } } // add 'em to the queues.. if ($joinType = $join->getJoinType()) { // real join if (!$fromClause) { $fromClause[] = $leftTable . $leftTableAlias; } $joinTables[] = $rightTable . $rightTableAlias; $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . " ON ({$condition})"; } else { // implicit join, translates to a where $fromClause[] = $leftTable . $leftTableAlias; $fromClause[] = $rightTable . $rightTableAlias; $whereClause[] = $condition; } } // Unique from clause elements $fromClause = array_unique($fromClause); $fromClause = array_diff($fromClause, array('')); // tables should not exist in both the from and join clauses if ($joinTables && $fromClause) { foreach ($fromClause as $fi => $ftable) { if (in_array($ftable, $joinTables)) { unset($fromClause[$fi]); } } } // Add the GROUP BY columns $groupByClause = $groupBy; $having = $criteria->getHaving(); $havingString = null; if ($having !== null) { $sb = ""; $having->appendPsTo($sb, $params); $havingString = $sb; } if (!empty($orderBy)) { foreach ($orderBy as $orderByColumn) { // Add function expression as-is. if (strpos($orderByColumn, '(') !== false) { $orderByClause[] = $orderByColumn; continue; } // Split orderByColumn (i.e. "table.column DESC") $dotPos = strrpos($orderByColumn, '.'); if ($dotPos !== false) { $tableName = substr($orderByColumn, 0, $dotPos); $columnName = substr($orderByColumn, $dotPos + 1); } else { $tableName = ''; $columnName = $orderByColumn; } $spacePos = strpos($columnName, ' '); if ($spacePos !== false) { $direction = substr($columnName, $spacePos); $columnName = substr($columnName, 0, $spacePos); } else { $direction = ''; } $tableAlias = $tableName; if ($aliasTableName = $criteria->getTableForAlias($tableName)) { $tableName = $aliasTableName; } $columnAlias = $columnName; if ($asColumnName = $criteria->getColumnForAs($columnName)) { $columnName = $asColumnName; } $column = $tableName ? $dbMap->getTable($tableName)->getColumn($columnName) : null; if ($criteria->isIgnoreCase() && $column && $column->isText()) { $orderByClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}") . $direction; $selectClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}"); } else { $orderByClause[] = $orderByColumn; } } } if (empty($fromClause) && $criteria->getPrimaryTableName()) { $fromClause[] = $criteria->getPrimaryTableName(); } // from / join tables quoten if it is necessary if ($db->useQuoteIdentifier()) { $fromClause = array_map(array($db, 'quoteIdentifierTable'), $fromClause); $joinClause = $joinClause ? $joinClause : array_map(array($db, 'quoteIdentifierTable'), $joinClause); } // build from-clause $from = ''; if (!empty($joinClause) && count($fromClause) > 1) { $from .= implode(" CROSS JOIN ", $fromClause); } else { $from .= implode(", ", $fromClause); } $from .= $joinClause ? ' ' . implode(' ', $joinClause) : ''; // Build the SQL from the arrays we compiled $sql = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " FROM " . $from . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : "") . ($havingString ? " HAVING " . $havingString : "") . ($orderByClause ? " ORDER BY " . implode(",", $orderByClause) : ""); // APPLY OFFSET & LIMIT to the query. if ($criteria->getLimit() || $criteria->getOffset()) { $db->applyLimit($sql, $criteria->getOffset(), $criteria->getLimit()); } return $sql; }