public function testCreateSelectSqlPart()
{
Propel::getServiceContainer()->setAdapter('oracle', new OracleAdapter());
$db = Propel::getServiceContainer()->getAdapter();
$c = new Criteria();
$c->addSelectColumn(BookTableMap::COL_ID);
$c->addAsColumn('book_ID', BookTableMap::COL_ID);
$fromClause = [];
$selectSql = $db->createSelectSqlPart($c, $fromClause);
$this->assertEquals('SELECT book.id, book.id AS book_ID', $selectSql, 'createSelectSqlPart() returns a SQL SELECT clause with both select and as columns');
$this->assertEquals(['book'], $fromClause, 'createSelectSqlPart() adds the tables from the select columns to the from clause');
}
public function testCreateSelectSqlPartAliasAll()
{
$db = Propel::getServiceContainer()->getAdapter(BookTableMap::DATABASE_NAME);
$c = new Criteria();
$c->addSelectColumn(BookTableMap::COL_ID);
$c->addAsColumn('book_id', BookTableMap::COL_ID);
$fromClause = [];
$selectSql = $db->createSelectSqlPart($c, $fromClause, true);
$this->assertEquals('SELECT book.id AS book_id_1, book.id AS book_id', $selectSql, 'createSelectSqlPart() aliases all columns if passed true as last parameter');
$this->assertEquals([], $fromClause, 'createSelectSqlPart() does not add the tables from an all-aliased list of select columns');
}
public function testMssqlApplyLimitWithOffsetMultipleOrderBy()
{
$db = Propel::getServiceContainer()->getAdapter(BookTableMap::DATABASE_NAME);
if (!$db instanceof MssqlAdapter) {
$this->markTestSkipped('Configured database vendor is not MsSQL');
}
$c = new Criteria(BookTableMap::DATABASE_NAME);
$c->addSelectColumn(BookTableMap::COL_ID);
$c->addSelectColumn(BookTableMap::COL_TITLE);
$c->addSelectColumn(PublisherTableMap::COL_NAME);
$c->addAsColumn('PublisherName', '(SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID)');
$c->addJoin(BookTableMap::COL_PUBLISHER_ID, PublisherTableMap::COL_ID, Criteria::LEFT_JOIN);
$c->addDescendingOrderByColumn('PublisherName');
$c->addAscendingOrderByColumn(BookTableMap::COL_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 = $c->createSelectSql($params);
$this->assertEquals($expectedSql, $sql);
}
/**
* Ensures uniqueness of select column names by turning them all into aliases
* This is necessary for queries on more than one table when the tables share a column name
*
* @see http://propel.phpdb.org/trac/ticket/795
*
* @param Criteria $criteria
* @return Criteria The input, with Select columns replaced by aliases
*/
public function turnSelectColumnsToAliases(Criteria $criteria)
{
$selectColumns = $criteria->getSelectColumns();
// clearSelectColumns also clears the aliases, so get them too
$asColumns = $criteria->getAsColumns();
$criteria->clearSelectColumns();
$columnAliases = $asColumns;
// add the select columns back
foreach ($selectColumns as $clause) {
// Generate a unique alias
$baseAlias = preg_replace('/\\W/', '_', $clause);
$alias = $baseAlias;
// If it already exists, add a unique suffix
$i = 0;
while (isset($columnAliases[$alias])) {
$i++;
$alias = $baseAlias . '_' . $i;
}
// Add it as an alias
$criteria->addAsColumn($alias, $clause);
$columnAliases[$alias] = $clause;
}
// Add the aliases back, don't modify them
foreach ($asColumns as $name => $clause) {
$criteria->addAsColumn($name, $clause);
}
return $criteria;
}
public function testHavingRaw()
{
$c = new Criteria();
$c->addSelectColumn(BookTableMap::TITLE);
$c->addAsColumn("isb_n", BookTableMap::ISBN);
$c->addHaving('isb_n = ?', '1234567890123', \PDO::PARAM_STR);
$expected = 'SELECT book.TITLE, book.ISBN AS isb_n FROM book HAVING isb_n = :p1';
$params = array();
$result = $c->createSelectSql($params);
$this->assertEquals($expected, $result);
$c->doSelect($this->con);
$expected = 'SELECT book.TITLE, book.ISBN AS isb_n FROM book HAVING isb_n = \'1234567890123\'';
$this->assertEquals($expected, $this->con->getLastExecutedQuery());
}
/**
* @expectedException \Propel\Runtime\Exception\LogicException
*/
public function testMergeWithAsColumnsThrowsException()
{
$c1 = new Criteria();
$c1->addAsColumn('foo', BookTableMap::COL_TITLE);
$c2 = new Criteria();
$c2->addAsColumn('foo', BookTableMap::COL_ID);
$c1->mergeWith($c2);
}
