/** * 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); }