protected function assertCriteriaTranslation($criteria, $expectedSql, $expectedParams, $message = '') { $params = array(); $result = BasePeer::createSelectSql($criteria, $params); $this->assertEquals($expectedSql, $result, $message); $this->assertEquals($expectedParams, $params, $message); }
public function testApplyLimitDuplicateColumnName() { Propel::setDb('oracle', new DBOracle()); $c = new Criteria(); $c->setDbName('oracle'); BookPeer::addSelectColumns($c); AuthorPeer::addSelectColumns($c); $c->setLimit(1); $params = array(); $sql = BasePeer::createSelectSql($c, $params); $this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS book_ID, book.TITLE AS book_TITLE, book.ISBN AS book_ISBN, book.PRICE AS book_PRICE, book.PUBLISHER_ID AS book_PUBLISHER_ID, book.AUTHOR_ID AS book_AUTHOR_ID, author.ID AS author_ID, author.FIRST_NAME AS author_FIRST_NAME, author.LAST_NAME AS author_LAST_NAME, author.EMAIL AS author_EMAIL, author.AGE AS author_AGESELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, author.ID, author.FIRST_NAME, author.LAST_NAME, author.EMAIL, author.AGE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found'); }
public function testApplyLimitDuplicateColumnNameWithColumn() { Propel::setDb('oracle', new DBOracle()); $c = new Criteria(); $c->setDbName('oracle'); BookPeer::addSelectColumns($c); AuthorPeer::addSelectColumns($c); $c->addAsColumn('BOOK_PRICE', BookPeer::PRICE); $c->setLimit(1); $params = array(); $asColumns = $c->getAsColumns(); $sql = BasePeer::createSelectSql($c, $params); $this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS ORA_COL_ALIAS_0, book.TITLE AS ORA_COL_ALIAS_1, book.ISBN AS ORA_COL_ALIAS_2, book.PRICE AS ORA_COL_ALIAS_3, book.PUBLISHER_ID AS ORA_COL_ALIAS_4, book.AUTHOR_ID AS ORA_COL_ALIAS_5, author.ID AS ORA_COL_ALIAS_6, author.FIRST_NAME AS ORA_COL_ALIAS_7, author.LAST_NAME AS ORA_COL_ALIAS_8, author.EMAIL AS ORA_COL_ALIAS_9, author.AGE AS ORA_COL_ALIAS_10, book.PRICE AS BOOK_PRICE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found'); $this->assertEquals($asColumns, $c->getAsColumns(), 'createSelectSql supplementary add alias column'); }
public function testApplyLimitDuplicateColumnNameWithColumn() { Propel::setDb('oracle', new DBOracle()); $c = new Criteria(); $c->setDbName('oracle'); BookPeer::addSelectColumns($c); AuthorPeer::addSelectColumns($c); $c->addAsColumn('BOOK_PRICE', BookPeer::PRICE); $c->setLimit(1); $params = array(); $asColumns = $c->getAsColumns(); $sql = BasePeer::createSelectSql($c, $params); $this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.id AS ORA_COL_ALIAS_0, book.title AS ORA_COL_ALIAS_1, book.isbn AS ORA_COL_ALIAS_2, book.price AS ORA_COL_ALIAS_3, book.publisher_id AS ORA_COL_ALIAS_4, book.author_id AS ORA_COL_ALIAS_5, author.id AS ORA_COL_ALIAS_6, author.first_name AS ORA_COL_ALIAS_7, author.last_name AS ORA_COL_ALIAS_8, author.email AS ORA_COL_ALIAS_9, author.age AS ORA_COL_ALIAS_10, book.price AS BOOK_PRICE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found'); $this->assertEquals($asColumns, $c->getAsColumns(), 'createSelectSql supplementary add alias column'); }
public static function getCriteriaCacheKey(Criteria $c) { $cloned_c = clone $c; $arr = array(); $str = BasePeer::createSelectSql($c, $arr); $param_map = array(); $keys = $c->keys(); foreach ($keys as $k) { $val = $c->getCriterion($k); if ($val) { $param_map[$k] = $val->hashCode(); } } $str .= print_r($param_map, true); $cache_key = md5($str); return $cache_key; }
public static function getMiSQL($criteria) { if (!isset($criteria)) { return null; } if (!$criteria->getSelectColumns()) { $criteria = clone $criteria; self::addSelectColumns($criteria); } $criteria->setDbName(self::DATABASE_NAME); $con = Propel::getConnection($criteria->getDbName()); $params = array(); $sql = BasePeer::createSelectSql($criteria, $params); $stmt = null; $stmt = $con->prepare($sql); $stmt->setLimit($criteria->getLimit()); $stmt->setOffset($criteria->getOffset()); return $stmt; }
public function testAddSelectModifier() { $c = new Criteria(); $c->setDistinct(); $c->addSelectModifier('SQL_CALC_FOUND_ROWS'); $this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier to the Criteria'); $c->addSelectModifier('SQL_CALC_FOUND_ROWS'); $this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier only once'); $params = array(); $result = BasePeer::createSelectSql($c, $params); $this->assertEquals('SELECT DISTINCT SQL_CALC_FOUND_ROWS FROM ', $result, 'addSelectModifier() adds a modifier to the final query'); }
/** * Do Explain Plan for query object or query string * * @param PropelPDO $con propel connection * @param ModelCriteria|string $query query the criteria or the query string * @throws PropelException * @return PDOStatement A PDO statement executed using the connection, ready to be fetched */ public function doExplainPlan(PropelPDO $con, $query) { if ($query instanceof ModelCriteria) { $params = array(); $dbMap = Propel::getDatabaseMap($query->getDbName()); $sql = BasePeer::createSelectSql($query, $params); $sql = 'EXPLAIN ' . $sql; } else { $sql = 'EXPLAIN ' . $query; } $stmt = $con->prepare($sql); if ($query instanceof ModelCriteria) { $this->bindValues($stmt, $params, $dbMap); } $stmt->execute(); return $stmt; }
public function testMssqlApplyLimitWithOffsetMultipleOrderBy() { $db = Propel::getDB(BookPeer::DATABASE_NAME); if (!$db instanceof DBMSSQL) { $this->markTestSkipped(); } $c = new Criteria(BookPeer::DATABASE_NAME); $c->addSelectColumn(BookPeer::ID); $c->addSelectColumn(BookPeer::TITLE); $c->addSelectColumn(PublisherPeer::NAME); $c->addAsColumn('PublisherName', '(SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID)'); $c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::LEFT_JOIN); $c->addDescendingOrderByColumn('PublisherName'); $c->addAscendingOrderByColumn(BookPeer::TITLE); $c->setOffset(20); $c->setLimit(20); $params = array(); $expectedSql = "SELECT [book.ID], [book.TITLE], [publisher.NAME], [PublisherName] FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) DESC, book.TITLE ASC) AS [RowNumber], book.ID AS [book.ID], book.TITLE AS [book.TITLE], publisher.NAME AS [publisher.NAME], (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) AS [PublisherName] FROM book LEFT JOIN publisher ON (book.PUBLISHER_ID=publisher.ID)) AS derivedb WHERE RowNumber BETWEEN 21 AND 40"; $sql = BasePeer::createSelectSql($c, $params); $this->assertEquals($expectedSql, $sql); }
protected function doCount($con) { $dbMap = Propel::getDatabaseMap($this->getDbName()); $db = Propel::getDB($this->getDbName()); // check that the columns of the main class are already added (if this is the primary ModelCriteria) if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) { $this->addSelfSelectColumns(); } $this->configureSelectColumns(); $needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers()) || count($this->selectQueries) > 0; $params = array(); if ($needsComplexCount) { if (BasePeer::needsSelectAliases($this)) { if ($this->getHaving()) { throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part'); } $db->turnSelectColumnsToAliases($this); } $selectSql = BasePeer::createSelectSql($this, $params); $sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt'; } else { // Replace SELECT columns with COUNT(*) $this->clearSelectColumns()->addSelectColumn('COUNT(*)'); $sql = BasePeer::createSelectSql($this, $params); } try { $stmt = $con->prepare($sql); $db->bindValues($stmt, $params, $dbMap); $stmt->execute(); } catch (Exception $e) { Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute COUNT statement [%s]', $sql), $e); } return $stmt; }
/** * Do Explain Plan for query object or query string * * @param PropelPDO $con propel connection * @param ModelCriteria|string $query query the criteria or the query string * @throws PropelException * @return PDOStatement A PDO statement executed using the connection, ready to be fetched */ public function doExplainPlan(PropelPDO $con, $query) { $con->beginTransaction(); if ($query instanceof ModelCriteria) { $params = array(); $dbMap = Propel::getDatabaseMap($query->getDbName()); $sql = BasePeer::createSelectSql($query, $params); } else { $sql = $query; } // unique id for the query string $uniqueId = uniqid('Propel', true); $stmt = $con->prepare($this->getExplainPlanQuery($sql, $uniqueId)); if ($query instanceof ModelCriteria) { $this->bindValues($stmt, $params, $dbMap); } $stmt->execute(); // explain plan is save in a table, data must be commit $con->commit(); $stmt = $con->prepare($this->getExplainPlanReadQuery($uniqueId)); $stmt->execute(); return $stmt; }
public function testUseFkQueryNested() { $q = ReviewQuery::create()->useBookQuery()->useAuthorQuery()->filterByFirstName('Leo')->endUse()->endUse(); $q1 = ReviewQuery::create()->join('Review.Book', Criteria::LEFT_JOIN)->join('Book.Author', Criteria::LEFT_JOIN)->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL); // embedded queries create joins that keep a relation to the parent // as this is not testable, we need to use another testing technique $params = array(); $result = BasePeer::createSelectSql($q, $params); $expectedParams = array(); $expectedResult = BasePeer::createSelectSql($q1, $expectedParams); $this->assertEquals($expectedParams, $params, 'useFkQuery() called nested creates two joins'); $this->assertEquals($expectedResult, $result, 'useFkQuery() called nested creates two joins'); }
public function testCombineAndFilterBy() { $params = array(); $sql = "SELECT FROM `book` WHERE ((book.title LIKE :p1 OR book.isbn LIKE :p2) AND book.title LIKE :p3)"; $c = BookQuery::create()->condition('u1', 'book.title LIKE ?', '%test1%')->condition('u2', 'book.isbn LIKE ?', '%test2%')->combine(array('u1', 'u2'), 'or')->filterByTitle('%test3%'); $result = BasePeer::createSelectSql($c, $params); $this->assertEquals($result, $sql); $params = array(); $sql = "SELECT FROM `book` WHERE (book.title LIKE :p1 AND (book.title LIKE :p2 OR book.isbn LIKE :p3))"; $c = BookQuery::create()->filterByTitle('%test3%')->condition('u1', 'book.title LIKE ?', '%test1%')->condition('u2', 'book.isbn LIKE ?', '%test2%')->combine(array('u1', 'u2'), 'or'); $result = BasePeer::createSelectSql($c, $params); $this->assertEquals($result, $sql); }
public function testaddUsingOperatorResetsDefaultOperator() { $c = new Criteria(); $c->addUsingOperator('foo1', 'bar1'); $c->_or(); $c->addUsingOperator('foo2', 'bar2'); $c->addUsingOperator('foo3', 'bar3'); $expected = 'SELECT FROM WHERE (foo1=:p1 OR foo2=:p2) AND foo3=:p3'; $params = array(); $result = BasePeer::createSelectSql($c, $params); $this->assertEquals($expected, $result); }
public function testDistinct() { $c = new Criteria(); $c->addSelectColumn('*'); $c->add('TABLE.string', 'foo'); $c->setDistinct(); $c->add('TABLE.id', 10); $c->setDistinct(); $params = array(); $result = BasePeer::createSelectSql($c, $params); $this->assertEquals('SELECT DISTINCT * FROM TABLE WHERE TABLE.string=:p1 AND TABLE.id=:p2', $result); }
/** * 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 = $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->setDB($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->setDB($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; }
/** * Returns the taggings associated to one tag or a set of tags. * * The second optionnal parameter permits to restrict the results with * different criterias * * @param mixed $tags Array of tag strings or string * @param array $options Array of options parameters * @return array */ public static function getTaggings($tags = array(), $options = array()) { $tags = sfPropelActAsTaggableToolkit::explodeTagString($tags); if (is_string($tags)) { $tags = array($tags); } $c = new Criteria(); $c->addJoin(TagPeer::ID, TaggingPeer::TAG_ID); if (count($tags) > 0) { $c->add(TagPeer::NAME, $tags, Criteria::IN); $having = $c->getNewCriterion('COUNT(' . TaggingPeer::TAGGABLE_MODEL . ') ', count($tags), Criteria::GREATER_EQUAL); $c->addHaving($having); } $c->addGroupByColumn(TaggingPeer::TAGGABLE_ID); $c->clearSelectColumns(); $c->addSelectColumn(TaggingPeer::TAGGABLE_MODEL); $c->addSelectColumn(TaggingPeer::TAGGABLE_ID); // Taggable model class option if (isset($options['model'])) { if (!class_exists($options['model']) || !is_callable(array(new $options['model'](), 'getPeer'))) { throw new PropelException(sprintf('The class "%s" does not exist, or it is not a model class.', $options['model'])); } $c->add(TaggingPeer::TAGGABLE_MODEL, $options['model']); } else { $c->addGroupByColumn(TaggingPeer::TAGGABLE_MODEL); } if (isset($options['triple'])) { $c->add(TagPeer::IS_TRIPLE, $options['triple']); } if (isset($options['namespace'])) { $c->add(TagPeer::TRIPLE_NAMESPACE, $options['namespace']); } if (isset($options['key'])) { $c->add(TagPeer::TRIPLE_KEY, $options['key']); } if (isset($options['value'])) { $c->add(TagPeer::TRIPLE_VALUE, $options['value']); } $param = array(); $sql = BasePeer::createSelectSql($c, $param); $con = Propel::getConnection(); if (Propel::VERSION < '1.3') { $stmt = $con->prepareStatement($sql); $position = 1; foreach ($tags as $tag) { $stmt->setString($position, $tag); $position++; } if (isset($options['model'])) { $stmt->setString($position, $options['model']); $position++; } if (isset($options['triple'])) { $stmt->setBoolean($position, $options['triple']); $position++; } if (isset($options['namespace'])) { $stmt->setString($position, $options['namespace']); $position++; } if (isset($options['key'])) { $stmt->setString($position, $options['key']); $position++; } if (isset($options['value'])) { $stmt->setString($position, $options['value']); $position++; } } else { $stmt = $con->prepare($sql); $position = 1; foreach ($tags as $tag) { $stmt->bindValue(':p' . $position, $tag, PDO::PARAM_STR); $position++; } if (isset($options['model'])) { $stmt->bindValue(':p' . $position, $options['model'], PDO::PARAM_STR); $position++; } if (isset($options['triple'])) { $stmt->bindValue(':p' . $position, $options['triple']); $position++; } if (isset($options['namespace'])) { $stmt->bindValue(':p' . $position, $options['namespace'], PDO::PARAM_STR); $position++; } if (isset($options['key'])) { $stmt->bindValue(':p' . $position, $options['key'], PDO::PARAM_STR); $position++; } if (isset($options['value'])) { $stmt->bindValue(':p' . $position, $options['value'], PDO::PARAM_STR); $position++; } } if (!isset($options['nb_common_tags']) || $options['nb_common_tags'] > count($tags)) { $options['nb_common_tags'] = count($tags); } if ($options['nb_common_tags'] > 0) { if (Propel::VERSION >= '1.3') { $stmt->bindValue(':p' . $position, $options['nb_common_tags'], PDO::PARAM_STR); } else { $stmt->setString($position, $options['nb_common_tags']); } } $taggings = array(); if (Propel::VERSION >= '1.3') { $rs = $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_NUM)) { $model = $row[0]; if (!isset($taggings[$model])) { $taggings[$model] = array(); } $taggings[$model][] = $row[1]; } } else { $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); while ($rs->next()) { $model = $rs->getString(1); if (!isset($taggings[$model])) { $taggings[$model] = array(); } $taggings[$model][] = $rs->getInt(2); } } return $taggings; }
/** * Produces a union-query from two queries. * * @todo Implement support for ORDER, LIMIT etc. * * @param ModelCriteria $mc1 * @param ModelCriteria $mc2 * @return mixed */ public static function union(ModelCriteria $mc1, ModelCriteria $mc2) { $dbMap = Propel::getDatabaseMap($mc1->getDbName()); $db = Propel::getDB($mc1->getDbName()); $con = Propel::getConnection($mc1->getDbName(), Propel::CONNECTION_READ); // we may modify criteria, so copy it first $c1 = clone $mc1; $c2 = clone $mc2; // check that the columns of the main class are already added (if this is the primary ModelCriteria) if (!$c1->hasSelectClause() && !$c1->getPrimaryCriteria()) { $c1->addSelfSelectColumns(); } if (!$c2->hasSelectClause() && !$c2->getPrimaryCriteria()) { $c2->addSelfSelectColumns(); } $con->beginTransaction(); try { $params = array(); $sql1 = BasePeer::createSelectSql($c1, $params); $sql2 = BasePeer::createSelectSql($c2, $params); $stmt = $con->prepare("({$sql1}) UNION ALL ({$sql2})"); $db->bindValues($stmt, $params, $dbMap); $stmt->execute(); $con->commit(); } catch (PropelException $e) { $con->rollback(); throw $e; } return $c1->getFormatter()->init($c1)->format($stmt); }
/** * Get the condition for Cases List * * @name getConditionCasesList * @param string $sTypeList * @param string $sUIDUserLogged * @param string $ClearSession * @param string $aAdditionalFilter * @return array */ public function getConditionCasesList($sTypeList = 'all', $sUIDUserLogged = '', $ClearSession = true, $aAdditionalFilter = null) { $c = new Criteria('workflow'); $c->clearSelectColumns(); $c->addSelectColumn(ApplicationPeer::APP_UID); $c->addSelectColumn(ApplicationPeer::APP_NUMBER); $c->addSelectColumn(ApplicationPeer::APP_UPDATE_DATE); $c->addSelectColumn(ApplicationPeer::PRO_UID); $c->addSelectColumn(ApplicationPeer::APP_INIT_USER); $c->addSelectColumn(AppDelegationPeer::DEL_PRIORITY); //$c->addSelectColumn(AppDelegationPeer::DEL_TASK_DUE_DATE); $c->addAsColumn( 'DEL_TASK_DUE_DATE', " IF (" . AppDelegationPeer::DEL_TASK_DUE_DATE . " <= NOW(), CONCAT('<span style=\'color:red\';>', " . AppDelegationPeer::DEL_TASK_DUE_DATE . ", '</span>'), " . AppDelegationPeer::DEL_TASK_DUE_DATE . ") " ); global $RBAC; //seems the PM_SUPERVISOR can delete a completed case if ($sTypeList == "completed" && $RBAC->userCanAccess('PM_SUPERVISOR') == 1) { $c->addAsColumn("DEL_LINK", "CONCAT('" . G::LoadTranslation('ID_DELETE') . "')"); } $c->addSelectColumn(AppDelegationPeer::DEL_INDEX); $c->addSelectColumn(AppDelegationPeer::TAS_UID); $c->addSelectColumn(AppDelegationPeer::DEL_INIT_DATE); $c->addSelectColumn(AppDelegationPeer::DEL_FINISH_DATE); $c->addSelectColumn(UsersPeer::USR_UID); $c->addAsColumn('APP_CURRENT_USER', "CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME)"); $c->addSelectColumn(ApplicationPeer::APP_STATUS); $c->addAsColumn('APP_TITLE', 'APP_TITLE.CON_VALUE'); $c->addAsColumn('APP_PRO_TITLE', 'PRO_TITLE.CON_VALUE'); $c->addAsColumn('APP_TAS_TITLE', 'TAS_TITLE.CON_VALUE'); //$c->addAsColumn('APP_DEL_PREVIOUS_USER', 'APP_LAST_USER.USR_USERNAME'); $c->addAsColumn( 'APP_DEL_PREVIOUS_USER', "CONCAT(APP_LAST_USER.USR_LASTNAME, ' ', APP_LAST_USER.USR_FIRSTNAME)" ); $c->addAlias("APP_TITLE", 'CONTENT'); $c->addAlias("PRO_TITLE", 'CONTENT'); $c->addAlias("TAS_TITLE", 'CONTENT'); $c->addAlias("APP_PREV_DEL", 'APP_DELEGATION'); $c->addAlias("APP_LAST_USER", 'USERS'); $c->addJoin(ApplicationPeer::APP_UID, AppDelegationPeer::APP_UID, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::TAS_UID, TaskPeer::TAS_UID, Criteria::LEFT_JOIN); $appThreadConds[] = array(ApplicationPeer::APP_UID, AppThreadPeer::APP_UID); $appThreadConds[] = array(AppDelegationPeer::DEL_INDEX, AppThreadPeer::DEL_INDEX); $c->addJoinMC($appThreadConds, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::USR_UID, UsersPeer::USR_UID, Criteria::LEFT_JOIN); $del = DBAdapter::getStringDelimiter(); $appTitleConds = array(); $appTitleConds[] = array(ApplicationPeer::APP_UID, 'APP_TITLE.CON_ID'); $appTitleConds[] = array('APP_TITLE.CON_CATEGORY', $del . 'APP_TITLE' . $del); $appTitleConds[] = array('APP_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($appTitleConds, Criteria::LEFT_JOIN); $proTitleConds = array(); $proTitleConds[] = array(ApplicationPeer::PRO_UID, 'PRO_TITLE.CON_ID'); $proTitleConds[] = array('PRO_TITLE.CON_CATEGORY', $del . 'PRO_TITLE' . $del); $proTitleConds[] = array('PRO_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($proTitleConds, Criteria::LEFT_JOIN); $tasTitleConds = array(); $tasTitleConds[] = array(AppDelegationPeer::TAS_UID, 'TAS_TITLE.CON_ID'); $tasTitleConds[] = array('TAS_TITLE.CON_CATEGORY', $del . 'TAS_TITLE' . $del); $tasTitleConds[] = array('TAS_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($tasTitleConds, Criteria::LEFT_JOIN); $prevConds = array(); $prevConds[] = array(ApplicationPeer::APP_UID, 'APP_PREV_DEL.APP_UID'); $prevConds[] = array('APP_PREV_DEL.DEL_INDEX', AppDelegationPeer::DEL_PREVIOUS); $c->addJoinMC($prevConds, Criteria::LEFT_JOIN); $usrConds = array(); $usrConds[] = array('APP_PREV_DEL.USR_UID', 'APP_LAST_USER.USR_UID'); $c->addJoinMC($usrConds, Criteria::LEFT_JOIN); $c->add(TaskPeer::TAS_TYPE, 'SUBPROCESS', Criteria::NOT_EQUAL); //gral, to_revise, to_reassign dont have userid in the query if ($sTypeList != 'gral' && $sTypeList != 'to_revise' && $sTypeList != 'to_reassign' && $sTypeList != 'my_started' && $sTypeList != 'sent') { $c->add(UsersPeer::USR_UID, $sUIDUserLogged); } /** * Additional filters * By Erik <*****@*****.**> */ if (isset($aAdditionalFilter) && is_array($aAdditionalFilter)) { foreach ($aAdditionalFilter as $sFilter => $sValue) { switch ($sFilter) { case 'PRO_UID': if ($sValue != "0") { $c->add(ApplicationPeer::PRO_UID, $sValue, Criteria::EQUAL); } break; case 'READ': $c->add(AppDelegationPeer::DEL_INIT_DATE, null, Criteria::ISNOTNULL); break; case 'UNREAD': $c->add(AppDelegationPeer::DEL_INIT_DATE, null, Criteria::ISNULL); break; } } } $filesList = array( //7 standard list 'to_do' => 'cases/cases_ListTodo', 'draft' => 'cases/cases_ListDraft', 'paused' => 'cases/cases_ListOnHold', 'cancelled' => 'cases/cases_ListCancelled', 'completed' => 'cases/cases_ListCompleted', 'sent' => 'cases/cases_ListSent', 'selfservice' => 'cases/cases_ListSelfService', //5 admin list 'all' => 'cases/cases_ListAll', 'to_revise' => 'cases/cases_ListToRevise', 'to_reassign' => 'cases/cases_ListAll_Reassign', 'my_started' => 'cases/cases_ListStarted', 'Alldelete' => 'cases/cases_ListAllDelete' ); switch ($sTypeList) { case 'all': $c->add( $c->getNewCriterion( AppThreadPeer::APP_THREAD_STATUS, 'OPEN')-> addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'COMPLETED')-> addAnd($c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0) ) ) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'my_started': $oCriteria = new Criteria('workflow'); $oCriteria->addSelectColumn(AppDelayPeer::APP_UID); $oCriteria->add( $oCriteria->getNewCriterion( AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL )->addOr( $oCriteria->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0) ) ); //$oCriteria->add(AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL); $oDataset = AppDelayPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); $oDataset->next(); $aProcesses = array(); while ($aRow = $oDataset->getRow()) { $aProcesses[] = $aRow['APP_UID']; $oDataset->next(); } $c->add($c->getNewCriterion(ApplicationPeer::APP_INIT_USER, $sUIDUserLogged)); $c->add( $c->getNewCriterion( AppThreadPeer::APP_THREAD_STATUS, 'OPEN' )->addOr( $c->getNewCriterion( ApplicationPeer::APP_STATUS, 'COMPLETED' )->addAnd( $c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0) ) ) ); $c->add($c->getNewCriterion(ApplicationPeer::APP_UID, $aProcesses, Criteria::NOT_IN)); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'to_do': $c->add(ApplicationPeer::APP_STATUS, 'TO_DO'); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN'); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'draft': $c->add(ApplicationPeer::APP_STATUS, 'DRAFT'); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'paused': $appDelayConds[] = array(ApplicationPeer::APP_UID, AppDelayPeer::APP_UID); $appDelayConds[] = array(AppDelegationPeer::DEL_INDEX, AppDelayPeer::APP_DEL_INDEX); $c->addJoinMC($appDelayConds, Criteria::LEFT_JOIN); $c->add(AppDelayPeer::APP_DELAY_UID, null, Criteria::ISNOTNULL); $c->add(AppDelayPeer::APP_TYPE, array("REASSIGN", "ADHOC", "CANCEL"), Criteria::NOT_IN); $c->add( $c->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL)-> addOr($c->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0)) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'cancelled': $c->add( $c->getNewCriterion(AppThreadPeer::APP_THREAD_STATUS, 'CLOSED')-> addAnd($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'CANCELLED')) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'completed': $c->add(ApplicationPeer::APP_STATUS, 'COMPLETED'); $c->add(AppDelegationPeer::DEL_PREVIOUS, '0', Criteria::NOT_EQUAL); //$c->addAsColumn('DEL_FINISH_DATE', 'max('.AppDelegationPeer::DEL_FINISH_DATE.')'); $c->addGroupByColumn(ApplicationPeer::APP_UID); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'gral': $c->add( $c->getNewCriterion(AppThreadPeer::APP_THREAD_STATUS, 'OPEN')-> addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'COMPLETED')-> addAnd($c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0))) ); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); $params = array(); $sSql = BasePeer::createSelectSql($c, $params); break; case 'to_revise': $oCriteria = new Criteria('workflow'); $oCriteria->add(ProcessUserPeer::USR_UID, $sUIDUserLogged); $oCriteria->add(ProcessUserPeer::PU_TYPE, 'SUPERVISOR'); $oDataset = ProcessUserPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); $oDataset->next(); $aProcesses = array(); while ($aRow = $oDataset->getRow()) { $aProcesses[] = $aRow['PRO_UID']; $oDataset->next(); } $c->add(ApplicationPeer::PRO_UID, $aProcesses, Criteria::IN); $c->add(ApplicationPeer::APP_STATUS, 'TO_DO'); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN'); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'selfservice': //get the valid selfservice tasks for this user $oCase = new Cases(); $tasks = $oCase->getSelfServiceTasks($_SESSION['USER_LOGGED']); $aTasks = array(); foreach ($tasks as $key => $val) { if (strlen(trim($val['uid'])) > 10) { $aTasks[] = $val['uid']; } } $c = new Criteria('workflow'); $c->clearSelectColumns(); $c->addSelectColumn(ApplicationPeer::APP_UID); $c->addSelectColumn(ApplicationPeer::APP_NUMBER); $c->addSelectColumn(ApplicationPeer::APP_UPDATE_DATE); $c->addSelectColumn(ApplicationPeer::PRO_UID); $c->addSelectColumn(ApplicationPeer::APP_INIT_USER); $c->addSelectColumn(AppDelegationPeer::DEL_PRIORITY); $c->addAsColumn( 'DEL_TASK_DUE_DATE', " IF (" . AppDelegationPeer::DEL_TASK_DUE_DATE . " <= NOW(), CONCAT('<span style=\'color:red\';>', " . AppDelegationPeer::DEL_TASK_DUE_DATE . ", '</span>'), " . AppDelegationPeer::DEL_TASK_DUE_DATE . ") " ); $c->addSelectColumn(AppDelegationPeer::DEL_INDEX); $c->addSelectColumn(AppDelegationPeer::TAS_UID); $c->addSelectColumn(AppDelegationPeer::DEL_INIT_DATE); $c->addSelectColumn(AppDelegationPeer::DEL_FINISH_DATE); $c->addSelectColumn(UsersPeer::USR_UID); $c->addAsColumn('APP_CURRENT_USER', "CONCAT(USERS.USR_LASTNAME, ' ', USERS.USR_FIRSTNAME)"); $c->addSelectColumn(ApplicationPeer::APP_STATUS); $c->addAsColumn('APP_TITLE', 'APP_TITLE.CON_VALUE'); $c->addAsColumn('APP_PRO_TITLE', 'PRO_TITLE.CON_VALUE'); $c->addAsColumn('APP_TAS_TITLE', 'TAS_TITLE.CON_VALUE'); $c->addAlias("APP_TITLE", 'CONTENT'); $c->addAlias("PRO_TITLE", 'CONTENT'); $c->addAlias("TAS_TITLE", 'CONTENT'); $c->addJoin(ApplicationPeer::APP_UID, AppDelegationPeer::APP_UID, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::TAS_UID, TaskPeer::TAS_UID, Criteria::LEFT_JOIN); $appThreadConds[] = array(ApplicationPeer::APP_UID, AppThreadPeer::APP_UID); $appThreadConds[] = array(AppDelegationPeer::DEL_INDEX, AppThreadPeer::DEL_INDEX); $c->addJoinMC($appThreadConds, Criteria::LEFT_JOIN); $c->addJoin(AppDelegationPeer::USR_UID, UsersPeer::USR_UID, Criteria::LEFT_JOIN); $del = DBAdapter::getStringDelimiter(); $appTitleConds = array(); $appTitleConds[] = array(ApplicationPeer::APP_UID, 'APP_TITLE.CON_ID'); $appTitleConds[] = array('APP_TITLE.CON_CATEGORY', $del . 'APP_TITLE' . $del); $appTitleConds[] = array('APP_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($appTitleConds, Criteria::LEFT_JOIN); // $proTitleConds = array(); $proTitleConds[] = array(ApplicationPeer::PRO_UID, 'PRO_TITLE.CON_ID'); $proTitleConds[] = array('PRO_TITLE.CON_CATEGORY', $del . 'PRO_TITLE' . $del); $proTitleConds[] = array('PRO_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($proTitleConds, Criteria::LEFT_JOIN); // $tasTitleConds = array(); $tasTitleConds[] = array(AppDelegationPeer::TAS_UID, 'TAS_TITLE.CON_ID'); $tasTitleConds[] = array('TAS_TITLE.CON_CATEGORY', $del . 'TAS_TITLE' . $del); $tasTitleConds[] = array('TAS_TITLE.CON_LANG', $del . SYS_LANG . $del); $c->addJoinMC($tasTitleConds, Criteria::LEFT_JOIN); $c->add(AppDelegationPeer::USR_UID, ''); $c->add(AppDelegationPeer::TAS_UID, $aTasks, Criteria::IN); break; case 'to_reassign': $c->add( $c->getNewCriterion(ApplicationPeer::APP_STATUS, 'TO_DO')-> addOr($c->getNewCriterion(ApplicationPeer::APP_STATUS, 'DRAFT')) ); $c->add(AppDelegationPeer::DEL_FINISH_DATE, null, Criteria::ISNULL); $c->add(AppThreadPeer::APP_THREAD_STATUS, 'OPEN'); $c->add(AppDelegationPeer::DEL_THREAD_STATUS, 'OPEN'); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; case 'sent': $oCriteria = new Criteria('workflow'); $oCriteria->addSelectColumn(AppDelayPeer::APP_UID); $oCriteria->add( $oCriteria->getNewCriterion( AppDelayPeer::APP_DISABLE_ACTION_USER, null, Criteria::ISNULL )->addOr( $oCriteria->getNewCriterion(AppDelayPeer::APP_DISABLE_ACTION_USER, 0) ) ); $oDataset = AppDelayPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); $oDataset->next(); $aProcesses = array(); while ($aRow = $oDataset->getRow()) { $aProcesses[] = $aRow['APP_UID']; $oDataset->next(); } if (isset($aAdditionalFilter) && isset($aAdditionalFilter['MINE'])) { $c->add($c->getNewCriterion(ApplicationPeer::APP_INIT_USER, $sUIDUserLogged)); } else { $c->add( $c->getNewCriterion( ApplicationPeer::APP_INIT_USER, $sUIDUserLogged )->addOr( $c->getNewCriterion( AppDelegationPeer::USR_UID, $sUIDUserLogged ) ) ); } if (isset($aAdditionalFilter) && isset($aAdditionalFilter['APP_STATUS_FILTER'])) { $c->add(ApplicationPeer::APP_STATUS, $sValue, Criteria::EQUAL); } else { $c->add(ApplicationPeer::APP_STATUS, 'DRAFT', Criteria::NOT_EQUAL); } $c->add( $c->getNewCriterion( AppDelegationPeer::DEL_THREAD_STATUS, 'CLOSED' )->addOr( $c->getNewCriterion( ApplicationPeer::APP_STATUS, 'COMPLETED' )->addAnd( $c->getNewCriterion(AppDelegationPeer::DEL_PREVIOUS, 0) ) ) ); $c->add($c->getNewCriterion(ApplicationPeer::APP_UID, $aProcesses, Criteria::NOT_IN)); $c->addDescendingOrderByColumn(ApplicationPeer::APP_NUMBER); break; } //select the xmlList to show if ($sTypeList == 'gral') { if ($RBAC->userCanAccess('PM_DELETECASE') == 1) { $xmlfile = $filesList['Alldelete']; } else { $xmlfile = $filesList['all']; } } else { $xmlfile = $filesList[$sTypeList]; } if ($ClearSession) { //OPCION_1: claening the $_SESSION and updating the List.... only case List foreach ($filesList as $file) { $id = G::createUID('', $file . '.xml'); unset($_SESSION['pagedTable[' . $id . ']']); unset($_SESSION[$id]); } //OPTION_2: cleaning the $_SESSION and whole List and xmls $cur = array_keys($_SESSION); foreach ($cur as $key) { if (substr($key, 0, 11) === "pagedTable[") { unset($_SESSION[$key]); } else { $xml = G::getUIDName($key, ''); if (strpos($xml, '.xml') !== false) { unset($_SESSION[$key]); } } } } return array($c, $xmlfile); }
/** * Test the Criteria::CUSTOM behavior. */ public function testCustomOperator() { $c = new Criteria(); $c->addSelectColumn('A.COL'); $c->add('A.COL', 'date_part(\'YYYY\', A.COL) = \'2007\'', Criteria::CUSTOM); $expected = "SELECT A.COL FROM A WHERE date_part('YYYY', A.COL) = '2007'"; $result = BasePeer::createSelectSql($c, $params = array()); $this->assertEquals($expected, $result); }
/** * Build a string representation of the Criteria. * * @return string A String with the representation of the Criteria. */ public function toString() { $sb = "Criteria:"; try { $params = array(); $sb .= "\nSQL (may not be complete): " . BasePeer::createSelectSql($this, $params); $sb .= "\nParams: "; $paramstr = array(); foreach ($params as $param) { $paramstr[] = $param['table'] . '.' . $param['column'] . ' => ' . var_export($param['value'], true); } $sb .= implode(", ", $paramstr); } catch (Exception $exc) { $sb .= "(Error: " . $exc->getMessage() . ")"; } return $sb; }
$headPublisher = &headPublisher::getSingleton(); //cambiar esto por PROPEL //CASE_CONSOLIDATED TASK $usrUid = $_SESSION["USER_LOGGED"]; $oCriteria = new Criteria("workflow"); $oCriteria->addSelectColumn("*"); $oCriteria->addSelectColumn(CaseConsolidatedPeer::TAS_UID); $oCriteria->addJoin(CaseConsolidatedPeer::TAS_UID,ContentPeer::CON_ID, Criteria::LEFT_JOIN); $oCriteria->addJoin(CaseConsolidatedPeer::TAS_UID,TaskPeer::TAS_UID, Criteria::LEFT_JOIN); $oCriteria->addAnd(ContentPeer::CON_CATEGORY, "TAS_TITLE"); $oCriteria->addAnd(ContentPeer::CON_LANG, "en"); $params = array(); //This will be filled with the parameters $sql = BasePeer::createSelectSql($oCriteria, $params); $oDataset = CaseConsolidatedPeer::doSelectRS($oCriteria); $oDataset->setFetchmode(ResultSet::FETCHMODE_ASSOC); //$oDataset->next(); while ($oDataset->next()) { $aRow = $oDataset->getRow(); //$aTaskConsolidated [] = $aRow; } $query = "SELECT * FROM CASE_CONSOLIDATED LEFT JOIN CONTENT ON (CASE_CONSOLIDATED.TAS_UID = CONTENT.CON_ID) LEFT JOIN TASK ON (CASE_CONSOLIDATED.TAS_UID = TASK.TAS_UID) WHERE CONTENT.CON_CATEGORY='TAS_TITLE' AND CONTENT.CON_LANG='en'"; $aTaskConsolidated = executeQuery($query);
protected function getCountStatement($con = null) { $dbMap = Propel::getDatabaseMap($this->getDbName()); $db = Propel::getDB($this->getDbName()); if ($con === null) { $con = Propel::getConnection($this->getDbName(), Propel::CONNECTION_READ); } // check that the columns of the main class are already added (if this is the primary ModelCriteria) if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) { $this->addSelfSelectColumns(); } $needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers()); $con->beginTransaction(); try { $this->basePreSelect($con); $params = array(); if ($needsComplexCount) { if (BasePeer::needsSelectAliases($this)) { if ($this->getHaving()) { throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part'); } BasePeer::turnSelectColumnsToAliases($this); } $selectSql = BasePeer::createSelectSql($this, $params); $sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt'; } else { // Replace SELECT columns with COUNT(*) $this->clearSelectColumns()->addSelectColumn('COUNT(*)'); $sql = BasePeer::createSelectSql($this, $params); } $stmt = $con->prepare($sql); BasePeer::populateStmtValues($stmt, $params, $dbMap, $db); $stmt->execute(); $con->commit(); } catch (PropelException $e) { $con->rollback(); throw $e; } return $stmt; }
/** * */ public function testMixedJoinOrder() { $this->markTestIncomplete(); $c = new Criteria(BookPeer::DATABASE_NAME); $c->addSelectColumn(BookPeer::ID); $c->addSelectColumn(BookPeer::TITLE); $c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::LEFT_JOIN); $c->addJoin(BookPeer::AUTHOR_ID, AuthorPeer::ID); $params = array(); $sql = BasePeer::createSelectSql($c, $params); $expectedSql = "SELECT book.ID, book.TITLE FROM book LEFT JOIN publisher ON (book.PUBLISHER_ID=publisher.ID), author WHERE book.AUTHOR_ID=author.ID"; // print $sql . "\n"; }
public function testClone() { $bookQuery1 = BookQuery::create()->filterByPrice(1); $bookQuery2 = clone $bookQuery1; $bookQuery2->filterByPrice(2); $params = array(); $sql = BasePeer::createSelectSql($bookQuery1, $params); $this->assertEquals('SELECT FROM `book` WHERE book.PRICE=:p1', $sql, 'conditions applied on a cloned query don\'t get applied on the original query'); }
public function testCombineDirtyOperators() { $this->c->addCond('cond1', "INVOICE.COST1", "1000", Criteria::GREATER_EQUAL); $this->c->addCond('cond2', "INVOICE.COST2", "2000", Criteria::LESS_EQUAL); $this->c->combine(array('cond1', 'cond2'), 'AnD', 'cond12'); $this->c->addCond('cond3', "INVOICE.COST3", "8000", Criteria::GREATER_EQUAL); $this->c->addCond('cond4', "INVOICE.COST4", "9000", Criteria::LESS_EQUAL); $this->c->combine(array('cond3', 'cond4'), 'aNd', 'cond34'); $this->c->combine(array('cond12', 'cond34'), 'oR'); $expect = "SELECT FROM INVOICE WHERE ((INVOICE.COST1>=:p1 AND INVOICE.COST2<=:p2) OR (INVOICE.COST3>=:p3 AND INVOICE.COST4<=:p4))"; $expect_params = array(array('table' => 'INVOICE', 'column' => 'COST1', 'value' => '1000'), array('table' => 'INVOICE', 'column' => 'COST2', 'value' => '2000'), array('table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'), array('table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000')); $params = array(); $result = BasePeer::createSelectSql($this->c, $params); $this->assertEquals($expect, $result); $this->assertEquals($expect_params, $params); }
/** * Build a string representation of the Criteria. * * @return string A String with the representation of the Criteria. */ public function toString() { $sb = "Criteria:: "; try { $sb .= "\nCurrent Query SQL (may not be complete or applicable): " . BasePeer::createSelectSql($this, $params = array()); $sb .= "\nParameters to replace: " . var_export($params, true); } catch (Exception $exc) { $sb .= "(Error: " . $exc->getMessage() . ")"; } return $sb; }
public static function doSelectWithTitle(Criteria $c, $culture = null, $include_unpublished_pages = false, $con = null) { $dbMap = Propel::getDatabaseMap($c->getDbName()); if ($con === null) { $con = Propel::getConnection($c->getDbName()); } if ($culture === null) { $culture = sfContext::getInstance()->getUser()->getCulture(); } // Set the correct dbName if it has not been overridden if ($c->getDbName() == Propel::getDefaultDB()) { $c->setDbName(self::DATABASE_NAME); } self::addSelectColumns($c); $startcol = self::NUM_COLUMNS - self::NUM_LAZY_LOAD_COLUMNS + 1; $c->addSelectColumn(sfSimpleCMSSlotPeer::VALUE); if (!$include_unpublished_pages) { $c->add(self::IS_PUBLISHED, true); } // Start of the complicated stuff // ------------------------------ // big hack to have the join operate on three conditions $c->addJoin(sfSimpleCMSSlotPeer::PAGE_ID, sfSimpleCMSPagePeer::ID . ' AND ' . sfSimpleCMSSlotPeer::CULTURE . ' = ? AND ' . sfSimpleCMSSlotPeer::NAME . ' = \'title\'', Criteria::RIGHT_JOIN); // but now we need to populate the statement by hand $params = array(); $sql = BasePeer::createSelectSql($c, $params); array_unshift($params, array('column' => sfSimpleCMSSlotPeer::CULTURE, 'table' => sfSimpleCMSSlotPeer::TABLE_NAME, 'value' => $culture)); $stmt = $con->prepareStatement($sql); $stmt->setLimit($c->getLimit()); $stmt->setOffset($c->getOffset()); $i = 1; foreach ($params as $param) { $tableName = $param['table']; $columnName = $param['column']; $value = $param['value']; if ($value === null) { $stmt->setNull($i++); } else { $cMap = $dbMap->getTable($tableName)->getColumn($columnName); $setter = 'set' . CreoleTypes::getAffix($cMap->getCreoleType()); $stmt->{$setter}($i++, $value); } } $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); // ---------------------------- // End of the complicated stuff /* // The complicated code above is there just to add a join on the three conditions // It could be achieved in a simpler way with these lines $c->addJoin(sfSimpleCMSSlotPeer::PAGE_ID, sfSimpleCMSPagePeer::ID, Criteria::RIGHT_JOIN); $c->add(sfSimpleCMSSlotPeer::CULTURE, $culture); $c->add(sfSimpleCMSSlotPeer::NAME, 'title'); // But then pages with no title would not be visible in menus // So we do it with more code and it's both safe and functional */ $results = array(); while ($rs->next()) { $page = new sfSimpleCMSPage(); $page->hydrate($rs); //$page->setCulture($culture); $page->setTitle($rs->getString($startcol)); $results[] = $page; } return $results; }