/**
* Test passing in a LIMIT with OFFSET clause string.
*/
public function testLimitSetFromClauseString()
{
$query = new SQLQuery();
$query->setSelect('*');
$query->setFrom('"SQLQueryTest_DO"');
$query->setLimit('20 OFFSET 10');
$limit = $query->getLimit();
$this->assertEquals(20, $limit['limit']);
$this->assertEquals(10, $limit['start']);
}
/**
* Convert a SQLQuery object into a SQL statement
* @param $query SQLQuery
*/
public function sqlQueryToString(SQLQuery $query)
{
if ($query->getDelete()) {
$text = 'DELETE ';
} else {
$text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct());
}
if ($query->getFrom()) {
$text .= $this->sqlFromToString($query->getFrom());
}
if ($query->getWhere()) {
$text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective());
}
// these clauses only make sense in SELECT queries, not DELETE
if (!$query->getDelete()) {
if ($query->getGroupBy()) {
$text .= $this->sqlGroupByToString($query->getGroupBy());
}
if ($query->getHaving()) {
$text .= $this->sqlHavingToString($query->getHaving());
}
if ($query->getOrderBy()) {
$text .= $this->sqlOrderByToString($query->getOrderBy());
}
if ($query->getLimit()) {
$text .= $this->sqlLimitToString($query->getLimit());
}
}
return $text;
}
/**
* Sets the page length, page start and total items from a query object's
* limit, offset and unlimited count. The query MUST have a limit clause.
*
* @param SQLQuery $query
*/
public function setPaginationFromQuery(SQLQuery $query)
{
if ($limit = $query->getLimit()) {
$this->setPageLength($limit['limit']);
$this->setPageStart($limit['start']);
$this->setTotalItems($query->unlimitedRowCount());
}
return $this;
}
/**
* Convert a SQLQuery object into a SQL statement
* Caution: Expects correctly quoted and escaped SQL fragments.
*
* @param $query SQLQuery
*/
public function sqlQueryToString(SQLQuery $query)
{
if ($query->getDelete()) {
//Appended space at the end of string causing an issue but this might not be the best solution
//@see sqlSelectToString() sqlFromToString
$text = 'DELETE';
} else {
if ($traverse = $query->getTraverse()) {
//Build the traverse string here
$text = $this->sqlTraverseToString($traverse);
} else {
$text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct());
}
}
if ($query->getFrom()) {
$text .= $this->sqlFromToString($query->getFrom());
}
if ($query->getWhere()) {
$text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective());
}
// these clauses only make sense in SELECT queries, not DELETE
if (!$query->getDelete()) {
if ($query->getGroupBy()) {
$text .= $this->sqlGroupByToString($query->getGroupBy());
}
if ($query->getHaving()) {
$text .= $this->sqlHavingToString($query->getHaving());
}
if ($query->getOrderBy()) {
$text .= $this->sqlOrderByToString($query->getOrderBy());
}
if ($query->getLimit()) {
$text .= $this->sqlLimitToString($query->getLimit());
}
}
// SS_Log::log(new Exception(print_r($text, true)), SS_Log::NOTICE);
return $text;
}
