public function testCreateSelectSqlPart() { Propel::setDb('oracle', new DBOracle()); $db = Propel::getDB(); $c = new Criteria(); $c->addSelectColumn(BookPeer::ID); $c->addAsColumn('book_ID', BookPeer::ID); $fromClause = array(); $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(array('book'), $fromClause, 'createSelectSqlPart() adds the tables from the select columns to the from clause'); }
/** * 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 * Moved from BasePeer to PdoAdapter and turned from static to non static * * @see http://propel.phpdb.org/trac/ticket/795 * * @param Propel\Runtime\Query\Criteria $criteria * @return Propel\Runtime\Query\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(BookPeer::TITLE); $c->addAsColumn("isb_n", BookPeer::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 = BasePeer::createSelectSql($c, $params); $this->assertEquals($expected, $result); BasePEer::doSelect($c, $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\PropelException */ public function testMergeWithAsColumnsThrowsException() { $c1 = new Criteria(); $c1->addAsColumn('foo', BookPeer::TITLE); $c2 = new Criteria(); $c2->addAsColumn('foo', BookPeer::ID); $c1->mergeWith($c2); }
public function testCreateSelectSqlPartAliasAll() { $db = Propel::getDB(BookPeer::DATABASE_NAME); $c = new Criteria(); $c->addSelectColumn(BookPeer::ID); $c->addAsColumn('book_ID', BookPeer::ID); $fromClause = array(); $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(array(), $fromClause, 'createSelectSqlPart() does not add the tables from an all-aliased list of select columns'); }
public function testMssqlApplyLimitWithOffsetMultipleOrderBy() { $db = Propel::getServiceContainer()->getAdapter(BookPeer::DATABASE_NAME); if (!$db instanceof MssqlAdapter) { $this->markTestSkipped('Configured database vendor is not MsSQL'); } $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); }
/** * Tests including aliases in criterion objects. * @link http://propel.phpdb.org/trac/ticket/636 */ public function testAliasInCriterion() { $c = new Criteria(); $c->addAsColumn("column_alias", "tbl.COL1"); $crit = $c->getNewCriterion("column_alias", "FOO"); $this->assertNull($crit->getTable()); $this->assertEquals("column_alias", $crit->getColumn()); $c->addHaving($crit); // produces invalid SQL referring to '.olumn_alias' }