/** * 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->setSelect("count(*)"); $countQuery->setFrom(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(); }
public function testParameterisedLeftJoins() { $query = new SQLSelect(); $query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"')); $query->setFrom('"SQLSelectTest_DO"'); $query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%')); $query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00')); $this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count" FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" = "SubSelect"."Title" WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters)); $this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters); $query->execute(); }
public function testSetWhereAny() { $query = new SQLSelect(); $query->setFrom('MyTable'); $query->setWhereAny(array('Monkey' => 'Chimp', 'Color' => 'Brown')); $sql = $query->sql($parameters); $this->assertSQLEquals("SELECT * FROM MyTable WHERE ((Monkey = ?) OR (Color = ?))", $sql); $this->assertEquals(array('Chimp', 'Brown'), $parameters); }