public function applyFilter(Criteria $criteria_to_filter)
{
if (!$this->enable) {
return;
}
if (!isset($criteria_to_filter->creteria_filter_attached)) {
$criteria_to_filter->creteria_filter_attached = true;
// copy all constraints from the criteria to $criteria_to_filter
$columns = $this->criteria->keys();
foreach ($columns as $column) {
$filter_criterion = $this->criteria->getCriterion($column);
$new_crit = $criteria_to_filter->getNewCriterion($filter_criterion->getTable() . "." . $filter_criterion->getColumn(), $filter_criterion->getValue(), $filter_criterion->getComparison());
$existing_criterion = $criteria_to_filter->getCriterion($column);
// don't add duplicates !!
if ($existing_criterion && ($existing_criterion->getValue() == $filter_criterion->getValue() && $existing_criterion->getComparison() == $filter_criterion->getComparison())) {
continue;
}
// go one step deeper to copy the inner clauses
$this->addClauses($this->criteria, $filter_criterion, $new_crit);
$criteria_to_filter->addAnd($new_crit);
}
// TODO - adda more robust way to copy the orderBy from this->criteria
$orderBy = $this->criteria->getOrderByColumns();
if ($orderBy) {
foreach ($orderBy as $orderByColumn) {
@(list($name, $order) = explode(" ", $orderByColumn));
if ($order == Criteria::ASC) {
$criteria_to_filter->addAscendingOrderByColumn($name);
} else {
$criteria_to_filter->addDescendingOrderByColumn($name);
}
}
}
}
}
public static function doSelect(Criteria $criteria, $con = null)
{
if (!count($criteria->getOrderByColumns())) {
$criteria->addAscendingOrderByColumn(sfFaqCategoryPeer::NAME);
}
return parent::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;
}
public function testMergeWithOrderByColumns()
{
$c1 = new Criteria();
$c1->addAscendingOrderByColumn(BookPeer::TITLE);
$c1->addAscendingOrderByColumn(BookPeer::ID);
$c2 = new Criteria();
$c1->mergeWith($c2);
$this->assertEquals(array(BookPeer::TITLE . ' ASC', BookPeer::ID . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() does not remove an existing orderby columns');
$c1 = new Criteria();
$c2 = new Criteria();
$c2->addAscendingOrderByColumn(BookPeer::TITLE);
$c2->addAscendingOrderByColumn(BookPeer::ID);
$c1->mergeWith($c2);
$this->assertEquals(array(BookPeer::TITLE . ' ASC', BookPeer::ID . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() merges the select columns to an empty order by');
$c1 = new Criteria();
$c1->addAscendingOrderByColumn(BookPeer::TITLE);
$c2 = new Criteria();
$c2->addAscendingOrderByColumn(BookPeer::ID);
$c1->mergeWith($c2);
$this->assertEquals(array(BookPeer::TITLE . ' ASC', BookPeer::ID . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() merges the select columns after the existing orderby columns');
$c1 = new Criteria();
$c1->addAscendingOrderByColumn(BookPeer::TITLE);
$c2 = new Criteria();
$c2->addAscendingOrderByColumn(BookPeer::TITLE);
$c1->mergeWith($c2);
$this->assertEquals(array(BookPeer::TITLE . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() does not merge duplicated orderby columns');
$c1 = new Criteria();
$c1->addAscendingOrderByColumn(BookPeer::TITLE);
$c2 = new Criteria();
$c2->addDescendingOrderByColumn(BookPeer::TITLE);
$c1->mergeWith($c2);
$this->assertEquals(array(BookPeer::TITLE . ' ASC', BookPeer::TITLE . ' DESC'), $c1->getOrderByColumns(), 'mergeWith() merges duplicated orderby columns with inverse direction');
}
/**
* Add the content of a Criteria to the current Criteria
* In case of conflict, the current Criteria keeps its properties
*
* @param Criteria $criteria The criteria to read properties from
* @param string $operator The logical operator used to combine conditions
* Defaults to Criteria::LOGICAL_AND, also accapts Criteria::LOGICAL_OR
* This parameter is deprecated, use _or() instead
*
* @return Criteria The current criteria object
*/
public function mergeWith(Criteria $criteria, $operator = null)
{
// merge limit
$limit = $criteria->getLimit();
if ($limit != 0 && $this->getLimit() == 0) {
$this->limit = $limit;
}
// merge offset
$offset = $criteria->getOffset();
if ($offset != 0 && $this->getOffset() == 0) {
$this->offset = $offset;
}
// merge select modifiers
$selectModifiers = $criteria->getSelectModifiers();
if ($selectModifiers && !$this->selectModifiers) {
$this->selectModifiers = $selectModifiers;
}
// merge select columns
$this->selectColumns = array_merge($this->getSelectColumns(), $criteria->getSelectColumns());
// merge as columns
$commonAsColumns = array_intersect_key($this->getAsColumns(), $criteria->getAsColumns());
if (!empty($commonAsColumns)) {
throw new PropelException('The given criteria contains an AsColumn with an alias already existing in the current object');
}
$this->asColumns = array_merge($this->getAsColumns(), $criteria->getAsColumns());
// merge orderByColumns
$orderByColumns = array_merge($this->getOrderByColumns(), $criteria->getOrderByColumns());
$this->orderByColumns = array_unique($orderByColumns);
// merge groupByColumns
$groupByColumns = array_merge($this->getGroupByColumns(), $criteria->getGroupByColumns());
$this->groupByColumns = array_unique($groupByColumns);
// merge where conditions
if ($operator == Criteria::LOGICAL_OR) {
$this->_or();
}
$isFirstCondition = true;
foreach ($criteria->getMap() as $key => $criterion) {
if ($isFirstCondition && $this->defaultCombineOperator == Criteria::LOGICAL_OR) {
$this->addOr($criterion, null, null, false);
$this->defaultCombineOperator == Criteria::LOGICAL_AND;
} elseif ($this->containsKey($key)) {
$this->addAnd($criterion);
} else {
$this->add($criterion);
}
$isFirstCondition = false;
}
// merge having
if ($having = $criteria->getHaving()) {
if ($this->getHaving()) {
$this->addHaving($this->getHaving()->addAnd($having));
} else {
$this->addHaving($having);
}
}
// merge alias
$commonAliases = array_intersect_key($this->getAliases(), $criteria->getAliases());
if (!empty($commonAliases)) {
throw new PropelException('The given criteria contains an alias already existing in the current object');
}
$this->aliases = array_merge($this->getAliases(), $criteria->getAliases());
// merge join
$this->joins = array_merge($this->getJoins(), $criteria->getJoins());
return $this;
}
/**
* 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;
}