/** * 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 \Propel\Runtime\Exception\RuntimeException Trouble creating the query string. */ public static function createSelectSql(Criteria $criteria, &$params) { $db = Propel::getServiceContainer()->getAdapter($criteria->getDbName()); $dbMap = Propel::getServiceContainer()->getDatabaseMap($criteria->getDbName()); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); // get the first part of the SQL statement, the SELECT part $selectSql = $db->createSelectSqlPart($criteria, $fromClause); // 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) { $join->setAdapter($db); // add 'em to the queues.. if (!$fromClause) { $fromClause[] = $join->getLeftTableWithAlias(); } $joinTables[] = $join->getRightTableWithAlias(); $joinClause[] = $join->getClause($params); } // 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->setAdapter($db); $sb = ''; $criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // 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(); } // tables should not exist as alias of subQuery if ($criteria->hasSelectQueries()) { foreach ($fromClause as $key => $ftable) { if (strpos($ftable, ' ') !== false) { list($realtable, $tableName) = explode(' ', $ftable); } else { $tableName = $ftable; } if ($criteria->hasSelectQuery($tableName)) { unset($fromClause[$key]); } } } // 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); } // add subQuery to From after adding quotes foreach ($criteria->getSelectQueries() as $subQueryAlias => $subQueryCriteria) { $fromClause[] = '(' . BasePeer::createSelectSql($subQueryCriteria, $params) . ') AS ' . $subQueryAlias; } // 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; }
/** * 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; }
public function testMergeWithGroupByColumns() { $c1 = new Criteria(); $c1->addGroupByColumn(BookPeer::TITLE); $c1->addGroupByColumn(BookPeer::ID); $c2 = new Criteria(); $c1->mergeWith($c2); $this->assertEquals(array(BookPeer::TITLE, BookPeer::ID), $c1->getGroupByColumns(), 'mergeWith() does not remove an existing groupby columns'); $c1 = new Criteria(); $c2 = new Criteria(); $c2->addGroupByColumn(BookPeer::TITLE); $c2->addGroupByColumn(BookPeer::ID); $c1->mergeWith($c2); $this->assertEquals(array(BookPeer::TITLE, BookPeer::ID), $c1->getGroupByColumns(), 'mergeWith() merges the select columns to an empty groupby'); $c1 = new Criteria(); $c1->addGroupByColumn(BookPeer::TITLE); $c2 = new Criteria(); $c2->addGroupByColumn(BookPeer::ID); $c1->mergeWith($c2); $this->assertEquals(array(BookPeer::TITLE, BookPeer::ID), $c1->getGroupByColumns(), 'mergeWith() merges the select columns after the existing groupby columns'); $c1 = new Criteria(); $c1->addGroupByColumn(BookPeer::TITLE); $c2 = new Criteria(); $c2->addGroupByColumn(BookPeer::TITLE); $c1->mergeWith($c2); $this->assertEquals(array(BookPeer::TITLE), $c1->getGroupByColumns(), 'mergeWith() does not merge duplicated groupby columns'); }