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);
                 }
             }
         }
     }
 }
Example #2
0
 public static function doSelect(Criteria $criteria, $con = null)
 {
     if (!count($criteria->getOrderByColumns())) {
         $criteria->addAscendingOrderByColumn(sfFaqCategoryPeer::NAME);
     }
     return parent::doSelect($criteria, $con);
 }
Example #3
0
 /**
  * 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;
 }
Example #4
0
 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');
 }
Example #5
0
 /**
  * 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;
 }
Example #8
0
 /**
  * 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;
 }