/** * @inheritdoc */ public function toCriteria() { $criteria = new Criteria(); if ($this->filterTransfer->getLimit() !== null) { $criteria->setLimit($this->filterTransfer->getLimit()); } if ($this->filterTransfer->getOffset() !== null) { $criteria->setOffset($this->filterTransfer->getOffset()); } if ($this->filterTransfer->getOrderBy() !== null) { if ($this->filterTransfer->getOrderDirection() === 'ASC') { $criteria->addAscendingOrderByColumn($this->filterTransfer->getOrderBy()); } elseif ($this->filterTransfer->getOrderDirection() === 'DESC') { $criteria->addDescendingOrderByColumn($this->filterTransfer->getOrderBy()); } } return $criteria; }
/** * Gets all the versions of this object, in incremental order * * @param ConnectionInterface $con the connection to use * * @return ObjectCollection A list of ChildContentVersion objects */ public function getAllVersions($con = null) { $criteria = new Criteria(); $criteria->addAscendingOrderByColumn(ContentVersionTableMap::VERSION); return $this->getContentVersions($criteria, $con); }
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); }
public function testOrderByIgnoreCase() { $originalDB = Propel::getServiceContainer()->getAdapter(); Propel::getServiceContainer()->setAdapter(Propel::getServiceContainer()->getDefaultDatasource(), new MysqlAdapter()); $criteria = new Criteria(); $criteria->setIgnoreCase(true); $criteria->addAscendingOrderByColumn(BookTableMap::TITLE); BookTableMap::addSelectColumns($criteria); $params = array(); $sql = $criteria->createSelectSql($params); $expectedSQL = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, UPPER(book.TITLE) FROM `book` ORDER BY UPPER(book.TITLE) ASC'; $this->assertEquals($expectedSQL, $sql); Propel::getServiceContainer()->setAdapter(Propel::getServiceContainer()->getDefaultDatasource(), $originalDB); }
public function testMergeWithOrderByColumns() { $c1 = new Criteria(); $c1->addAscendingOrderByColumn(BookTableMap::COL_TITLE); $c1->addAscendingOrderByColumn(BookTableMap::COL_ID); $c2 = new Criteria(); $c1->mergeWith($c2); $this->assertEquals(array(BookTableMap::COL_TITLE . ' ASC', BookTableMap::COL_ID . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() does not remove an existing orderby columns'); $c1 = new Criteria(); $c2 = new Criteria(); $c2->addAscendingOrderByColumn(BookTableMap::COL_TITLE); $c2->addAscendingOrderByColumn(BookTableMap::COL_ID); $c1->mergeWith($c2); $this->assertEquals(array(BookTableMap::COL_TITLE . ' ASC', BookTableMap::COL_ID . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() merges the select columns to an empty order by'); $c1 = new Criteria(); $c1->addAscendingOrderByColumn(BookTableMap::COL_TITLE); $c2 = new Criteria(); $c2->addAscendingOrderByColumn(BookTableMap::COL_ID); $c1->mergeWith($c2); $this->assertEquals(array(BookTableMap::COL_TITLE . ' ASC', BookTableMap::COL_ID . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() merges the select columns after the existing orderby columns'); $c1 = new Criteria(); $c1->addAscendingOrderByColumn(BookTableMap::COL_TITLE); $c2 = new Criteria(); $c2->addAscendingOrderByColumn(BookTableMap::COL_TITLE); $c1->mergeWith($c2); $this->assertEquals(array(BookTableMap::COL_TITLE . ' ASC'), $c1->getOrderByColumns(), 'mergeWith() does not merge duplicated orderby columns'); $c1 = new Criteria(); $c1->addAscendingOrderByColumn(BookTableMap::COL_TITLE); $c2 = new Criteria(); $c2->addDescendingOrderByColumn(BookTableMap::COL_TITLE); $c1->mergeWith($c2); $this->assertEquals(array(BookTableMap::COL_TITLE . ' ASC', BookTableMap::COL_TITLE . ' DESC'), $c1->getOrderByColumns(), 'mergeWith() merges duplicated orderby columns with inverse direction'); }
public function testOrderByIgnoreCase() { $originalDB = Propel::getServiceContainer()->getAdapter(); Propel::getServiceContainer()->setAdapter(Propel::getServiceContainer()->getDefaultDatasource(), new MysqlAdapter()); Propel::getServiceContainer()->setDefaultDatasource('bookstore'); $criteria = new Criteria(); $criteria->setIgnoreCase(true); $criteria->addAscendingOrderByColumn(BookTableMap::COL_TITLE); BookTableMap::addSelectColumns($criteria); $params = []; $sql = $criteria->createSelectSql($params); $expectedSQL = 'SELECT book.id, book.title, book.isbn, book.price, book.publisher_id, book.author_id, UPPER(book.title) FROM book ORDER BY UPPER(book.title) ASC'; $this->assertEquals($expectedSQL, $sql); Propel::getServiceContainer()->setAdapter(Propel::getServiceContainer()->getDefaultDatasource(), $originalDB); }
/** * Update the tree to allow insertion of a leaf at the specified position * * @param ConnectionInterface $con Connection to use. */ public static function fixLevels(ConnectionInterface $con = null) { $c = new Criteria(); $c->addAscendingOrderByColumn(ChildCategory::LEFT_COL); $dataFetcher = ChildCategoryQuery::create(null, $c)->setFormatter(ModelCriteria::FORMAT_STATEMENT)->find($con); // set the class once to avoid overhead in the loop $cls = CategoryTableMap::getOMClass(false); $level = null; // iterate over the statement while ($row = $dataFetcher->fetch()) { // hydrate object $key = CategoryTableMap::getPrimaryKeyHashFromRow($row, 0); /** @var $obj ChildCategory */ if (null === ($obj = CategoryTableMap::getInstanceFromPool($key))) { $obj = new $cls(); $obj->hydrate($row); CategoryTableMap::addInstanceToPool($obj, $key); } // compute level // Algorithm shamelessly stolen from sfPropelActAsNestedSetBehaviorPlugin // Probably authored by Tristan Rivoallan if ($level === null) { $level = 0; $i = 0; $prev = array($obj->getRightValue()); } else { while ($obj->getRightValue() > $prev[$i]) { $i--; } $level = ++$i; $prev[$i] = $obj->getRightValue(); } // update level in node if necessary if ($obj->getLevel() !== $level) { $obj->setLevel($level); $obj->save($con); } } $dataFetcher->close(); }
/** * Test inheritance features. */ public function testInheritance() { $manager = new BookstoreManager(); $manager->setName("Manager 1"); $manager->setJobTitle("Warehouse Manager"); $manager->save(); $managerId = $manager->getId(); $employee = new BookstoreEmployee(); $employee->setName("Employee 1"); $employee->setJobTitle("Janitor"); $employee->setSupervisorId($managerId); $employee->save(); $empId = $employee->getId(); $cashier = new BookstoreCashier(); $cashier->setName("Cashier 1"); $cashier->setJobTitle("Cashier"); $cashier->save(); $cashierId = $cashier->getId(); // 1) test the pooled instances' $c = new Criteria(); $c->add(BookstoreEmployeeTableMap::ID, [$managerId, $empId, $cashierId], Criteria::IN); $c->addAscendingOrderByColumn(BookstoreEmployeeTableMap::ID); $objects = BookstoreEmployeeQuery::create()->doSelect($c); $this->assertEquals(3, count($objects), "Expected 3 objects to be returned."); list($o1, $o2, $o3) = $objects; $this->assertSame($o1, $manager); $this->assertSame($o2, $employee); $this->assertSame($o3, $cashier); // 2) test a forced reload from database BookstoreEmployeeTableMap::clearInstancePool(); list($o1, $o2, $o3) = BookstoreEmployeeQuery::create()->doSelect($c); $this->assertTrue($o1 instanceof BookstoreManager, "Expected BookstoreManager object, got " . get_class($o1)); $this->assertTrue($o2 instanceof BookstoreEmployee, "Expected BookstoreEmployee object, got " . get_class($o2)); $this->assertTrue($o3 instanceof BookstoreCashier, "Expected BookstoreCashier object, got " . get_class($o3)); }