public static function doBackupRecord(\Criteria $criteria, PropelPDO $con) { $db = Propel::getDB($criteria->getDbName()); $dbMap = Propel::getDatabaseMap($criteria->getDbName()); $keys = $criteria->keys(); if (!empty($keys)) { $tableName = $criteria->getTableName($keys[0]); } else { throw new PropelException("Database insert attempted without anything specified to insert"); } $tableMap = $dbMap->getTable($tableName); $whereClause = array(); $peer = $tableMap->getPeerClassname(); $versionTable = $peer::$workspaceBehaviorVersionName; $originTable = $tableMap->getName(); $tables = $criteria->getTablesColumns(); if (empty($tables)) { throw new \PropelException("Empty Criteria"); } $fields = array_keys($tableMap->getColumns()); $fields = implode(', ', $fields); foreach ($tables as $tableName => $columns) { $whereClause = array(); $params = array(); $stmt = null; try { foreach ($columns as $colName) { $sb = ""; $criteria->getCriterion($colName)->appendPsTo($sb, $params); $whereClause[] = $sb; } $sql = sprintf("INSERT INTO %s (%s) SELECT %s FROM %s WHERE %s", $versionTable, $fields, $fields, $originTable, implode(" AND ", $whereClause)); $stmt = $con->prepare($sql); $db->bindValues($stmt, $params, $dbMap); $stmt->execute(); $stmt->closeCursor(); } catch (Exception $e) { Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute INSERT INTO statement [%s]', $sql), $e); } } // for each table }
/** * 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, sql may be wrong", Propel::LOG_ERR); } // init $this->realtable $realtable = $criteria->getTableForAlias($this->table); $this->realtable = $realtable ? $realtable : $this->table; }
/** * Prepares the Criteria object and uses the parent doSelect() * method to get a ResultSet. * * Use this method directly if you want to just get the resultset * (instead of an array of objects). * * @param Criteria $criteria The Criteria object used to build the SELECT statement. * @param Connection $con the connection to use * @throws PropelException Any exceptions caught during processing will be * rethrown wrapped into a PropelException. * @return ResultSet The resultset object with numerically-indexed fields. * @see BasePeer::doSelect() */ public static function doSelectRS(Criteria $criteria, $con = null) { if ($con === null) { //$con = Propel::getConnection(self::DATABASE_NAME); $con = Propel::getConnection($criteria->getDbName()); } if (!$criteria->getSelectColumns()) { $criteria = clone $criteria; //ApplicationPeer::addSelectColumns($criteria); } // Set the correct dbName //$criteria->setDbName(self::DATABASE_NAME); $criteria->setDbName($criteria->getDbName()); // BasePeer returns a Creole ResultSet, set to return // rows indexed numerically. return BasePeer::doSelect($criteria, $con); }
/** * 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()); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); // get the first part of the SQL statement, the SELECT part $selectSql = self::createSelectSqlPart($criteria, $fromClause); // add the criteria to WHERE clause // this will also add the table names to the FROM clause if they are not already // included via a LEFT JOIN foreach ($criteria->keys() as $key) { $criterion = $criteria->getCriterion($key); $table = null; foreach ($criterion->getAttachedCriterion() as $attachedCriterion) { $tableName = $attachedCriterion->getTable(); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } if (($criteria->isIgnoreCase() || $attachedCriterion->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($attachedCriterion->getColumn())->isText()) { $attachedCriterion->setIgnoreCase(true); } } $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 ($criteria->getJoins() as $join) { // The join might have been established using an alias name $leftTable = $join->getLeftTableName(); if ($realTable = $criteria->getTableForAlias($leftTable)) { $leftTableForFrom = $realTable . ' ' . $leftTable; $leftTable = $realTable; } else { $leftTableForFrom = $leftTable; } $rightTable = $join->getRightTableName(); if ($realTable = $criteria->getTableForAlias($rightTable)) { $rightTableForFrom = $realTable . ' ' . $rightTable; $rightTable = $realTable; } else { $rightTableForFrom = $rightTable; } // 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[] = $leftTableForFrom; } $joinTables[] = $rightTableForFrom; $joinClause[] = $join->getJoinType() . ' ' . $rightTableForFrom . " ON ({$condition})"; } else { // implicit join, translates to a where $fromClause[] = $leftTableForFrom; $fromClause[] = $rightTableForFrom; $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()) { $ignoreCaseColumn = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}"); $orderByClause[] = $ignoreCaseColumn . $direction; $selectSql .= ', ' . $ignoreCaseColumn; } else { $orderByClause[] = $orderByColumn; } } } if (empty($fromClause) && $criteria->getPrimaryTableName()) { $fromClause[] = $criteria->getPrimaryTableName(); } // from / join tables quoted 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 = $selectSql . " 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(), $criteria); } return $sql; }
/** * Method to do selects. * * @param Criteria $criteria The Criteria object used to build the SELECT statement. * @param Connection $con * @return array Array of selected Objects * @throws PropelException Any exceptions caught during processing will be * rethrown wrapped into a PropelException. */ public static function doSelect(Criteria $criteria, $tableName, $con = null) { $dbMap = Propel::getDatabaseMap($criteria->getDbName()); $stmt = null; try { $params = array(); $sql = self::createSelectSql($criteria, $tableName, $params); $sql['params'] = $params; $stmt = $con->prepareStatement($sql); //$stmt->setLimit($criteria->getLimit()); $sql['limit'] = $criteria->getLimit(); //$stmt->setOffset($criteria->getOffset()); $sql['offset'] = $criteria->getOffset(); //$rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); $rs = $con->executeQuery($sql, ResultSet::FETCHMODE_NUM); } catch (Exception $e) { if ($stmt) { $stmt->close(); } throw new PropelException($e); } return $rs; }
/** * 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; }
public static function doSelectWithI18n(Criteria $c, $culture = null, $con = null) { if ($culture === null) { $culture = sfContext::getInstance()->getUser()->getCulture(); } if ($c->getDbName() == Propel::getDefaultDB()) { $c->setDbName(self::DATABASE_NAME); } JobPeer::addSelectColumns($c); $startcol = JobPeer::NUM_COLUMNS - JobPeer::NUM_LAZY_LOAD_COLUMNS + 1; JobI18nPeer::addSelectColumns($c); $c->addJoin(JobPeer::ID, JobI18nPeer::ID); $c->add(JobI18nPeer::CULTURE, $culture); $rs = BasePeer::doSelect($c, $con); $results = array(); while ($rs->next()) { $omClass = JobPeer::getOMClass(); $cls = Propel::import($omClass); $obj1 = new $cls(); $obj1->hydrate($rs); $obj1->setCulture($culture); $omClass = JobI18nPeer::getOMClass($rs, $startcol); $cls = Propel::import($omClass); $obj2 = new $cls(); $obj2->hydrate($rs, $startcol); $obj1->setJobI18nForCulture($obj2, $culture); $obj2->setJob($obj1); $results[] = $obj1; } return $results; }
public static function doSelectWithTitle(Criteria $c, $culture = null, $include_unpublished_pages = false, $con = null) { $dbMap = Propel::getDatabaseMap($c->getDbName()); if ($con === null) { $con = Propel::getConnection($c->getDbName()); } if ($culture === null) { $culture = sfContext::getInstance()->getUser()->getCulture(); } // Set the correct dbName if it has not been overridden if ($c->getDbName() == Propel::getDefaultDB()) { $c->setDbName(self::DATABASE_NAME); } self::addSelectColumns($c); $startcol = self::NUM_COLUMNS - self::NUM_LAZY_LOAD_COLUMNS + 1; $c->addSelectColumn(sfSimpleCMSSlotPeer::VALUE); if (!$include_unpublished_pages) { $c->add(self::IS_PUBLISHED, true); } // Start of the complicated stuff // ------------------------------ // big hack to have the join operate on three conditions $c->addJoin(sfSimpleCMSSlotPeer::PAGE_ID, sfSimpleCMSPagePeer::ID . ' AND ' . sfSimpleCMSSlotPeer::CULTURE . ' = ? AND ' . sfSimpleCMSSlotPeer::NAME . ' = \'title\'', Criteria::RIGHT_JOIN); // but now we need to populate the statement by hand $params = array(); $sql = BasePeer::createSelectSql($c, $params); array_unshift($params, array('column' => sfSimpleCMSSlotPeer::CULTURE, 'table' => sfSimpleCMSSlotPeer::TABLE_NAME, 'value' => $culture)); $stmt = $con->prepareStatement($sql); $stmt->setLimit($c->getLimit()); $stmt->setOffset($c->getOffset()); $i = 1; foreach ($params as $param) { $tableName = $param['table']; $columnName = $param['column']; $value = $param['value']; if ($value === null) { $stmt->setNull($i++); } else { $cMap = $dbMap->getTable($tableName)->getColumn($columnName); $setter = 'set' . CreoleTypes::getAffix($cMap->getCreoleType()); $stmt->{$setter}($i++, $value); } } $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); // ---------------------------- // End of the complicated stuff /* // The complicated code above is there just to add a join on the three conditions // It could be achieved in a simpler way with these lines $c->addJoin(sfSimpleCMSSlotPeer::PAGE_ID, sfSimpleCMSPagePeer::ID, Criteria::RIGHT_JOIN); $c->add(sfSimpleCMSSlotPeer::CULTURE, $culture); $c->add(sfSimpleCMSSlotPeer::NAME, 'title'); // But then pages with no title would not be visible in menus // So we do it with more code and it's both safe and functional */ $results = array(); while ($rs->next()) { $page = new sfSimpleCMSPage(); $page->hydrate($rs); //$page->setCulture($culture); $page->setTitle($rs->getString($startcol)); $results[] = $page; } return $results; }
/** * Executes a COUNT query using either a simple SQL rewrite or, for more complex queries, a * sub-select of the SQL created by createSelectSql() and returns the statement. * * @param Criteria $criteria A Criteria. * @param PropelPDO $con A PropelPDO connection to use. * @return PDOStatement The resultset statement. * @throws PropelException * @see createSelectSql() */ public static function doCount(Criteria $criteria, PropelPDO $con = null) { $dbMap = Propel::getDatabaseMap($criteria->getDbName()); $db = Propel::getDB($criteria->getDbName()); if ($con === null) { $con = Propel::getConnection($criteria->getDbName(), Propel::CONNECTION_READ); } $stmt = null; if ($criteria->isUseTransaction()) { $con->beginTransaction(); } $needsComplexCount = $criteria->getGroupByColumns() || $criteria->getOffset() || $criteria->getLimit() || $criteria->getHaving() || in_array(Criteria::DISTINCT, $criteria->getSelectModifiers()); try { $params = array(); if ($needsComplexCount) { $selectSql = self::createSelectSql($criteria, $params); $sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') AS propelmatch4cnt'; } else { // Replace SELECT columns with COUNT(*) $criteria->clearSelectColumns()->addSelectColumn('COUNT(*)'); $sql = self::createSelectSql($criteria, $params); } $stmt = $con->prepare($sql); self::populateStmtValues($stmt, $params, $dbMap, $db); $stmt->execute(); if ($criteria->isUseTransaction()) { $con->commit(); } } catch (Exception $e) { if ($stmt) { $stmt = null; } // close if ($criteria->isUseTransaction()) { $con->rollBack(); } Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException($e); } return $stmt; }