public function testSelectWithOrderbyClause() { $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('MyName'); $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('MyName desc'); $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('MyName ASC, Color DESC'); $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color DESC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('MyName ASC, Color'); $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color ASC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy(array('MyName' => 'desc')); $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy(array('MyName' => 'desc', 'Color')); $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC, Color ASC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('implode("MyName","Color")'); $this->assertSQLEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('implode("MyName","Color") DESC'); $this->assertSQLEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" DESC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->setOrderBy('RAND()'); $this->assertSQLEquals('SELECT *, RAND() AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql($parameters)); $query = new SQLSelect(); $query->setFrom("MyTable"); $query->addFrom('INNER JOIN SecondTable USING (ID)'); $query->addFrom('INNER JOIN ThirdTable USING (ID)'); $query->setOrderBy('MyName'); $this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN SecondTable USING (ID) ' . 'INNER JOIN ThirdTable USING (ID) ' . 'ORDER BY MyName ASC', $query->sql($parameters)); }
/** * Return the number of rows in this query if the limit were removed. Useful in paged data sets. * * @param string $column * @return int */ public function unlimitedRowCount($column = null) { // we can't clear the select if we're relying on its output by a HAVING clause if (count($this->having)) { $records = $this->execute(); return $records->numRecords(); } $clone = clone $this; $clone->limit = null; $clone->orderby = null; // Choose a default column if ($column == null) { if ($this->groupby) { // @todo Test case required here $countQuery = new SQLSelect(); $countQuery->select("count(*)"); $countQuery->addFrom(array('(' . $clone->sql($innerParameters) . ') all_distinct')); $sql = $countQuery->sql($parameters); // $parameters should be empty $result = DB::prepared_query($sql, $innerParameters); return $result->value(); } else { $clone->setSelect(array("count(*)")); } } else { $clone->setSelect(array("count({$column})")); } $clone->setGroupBy(array()); return $clone->execute()->value(); }