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'); }
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); }