/**
* Select the given fields from the given table.
*
* @param String $table Unquoted table name (will be escaped automatically)
* @param Array $fields Database column names (will be escaped automatically)
*/
public function selectFromTable($table, $fields)
{
$fieldExpressions = array_map(function ($item) use($table) {
return "\"{$table}\".\"{$item}\"";
}, $fields);
$this->query->setSelect($fieldExpressions);
return $this;
}
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();
}
/**
* Test passing in a LIMIT with OFFSET clause string.
*/
public function testLimitSetFromClauseString()
{
$query = new SQLSelect();
$query->setSelect('*');
$query->setFrom('"SQLQueryTest_DO"');
$query->setLimit('20 OFFSET 10');
$limit = $query->getLimit();
$this->assertEquals(20, $limit['limit']);
$this->assertEquals(10, $limit['start']);
}
/**
* 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 testDbDatetimeDifference()
{
$offset = $this->checkPreconditions();
$clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset);
$clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -15, '15 seconds ago - now', $offset);
$clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset);
$query = new SQLSelect();
$query->setSelect(array());
$query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1);
$result = $query->execute()->value();
$lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
$created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
$this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset);
}
