/** * 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 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; }