/**
* Convert a SQLQuery object into a SQL statement.
*/
public function sqlQueryToString(SQLQuery $sqlQuery)
{
if (!$sqlQuery->from) {
return '';
}
if ($sqlQuery->orderby && strtoupper(trim($sqlQuery->orderby)) == 'RAND()') {
$sqlQuery->orderby = "NEWID()";
}
//Get the limit and offset
$limit = '';
$offset = '0';
if (is_array($sqlQuery->limit)) {
$limit = $sqlQuery->limit['limit'];
if (isset($sqlQuery->limit['start'])) {
$offset = $sqlQuery->limit['start'];
}
} else {
if (preg_match('/^([0-9]+) offset ([0-9]+)$/i', trim($sqlQuery->limit), $matches)) {
$limit = $matches[1];
$offset = $matches[2];
} else {
//could be a comma delimited string
$bits = explode(',', $sqlQuery->limit);
if (sizeof($bits) > 1) {
list($offset, $limit) = $bits;
} else {
$limit = $bits[0];
}
}
}
$text = '';
$suffixText = '';
$nestedQuery = false;
// DELETE queries
if ($sqlQuery->delete) {
$text = 'DELETE ';
// SELECT queries
} else {
$distinct = $sqlQuery->distinct ? "DISTINCT " : "";
// If there's a limit but no offset, just use 'TOP X'
// rather than the more complex sub-select method
if ($limit != 0 && $offset == 0) {
$text = "SELECT {$distinct} TOP {$limit}";
// If there's a limit and an offset, then we need to do a subselect
} else {
if ($limit && $offset) {
if ($sqlQuery->orderby) {
$rowNumber = "ROW_NUMBER() OVER (ORDER BY {$sqlQuery->orderby}) AS Number";
} else {
$firstCol = reset($sqlQuery->select);
$rowNumber = "ROW_NUMBER() OVER (ORDER BY {$firstCol}) AS Number";
}
$text = "SELECT * FROM ( SELECT {$distinct}{$rowNumber}, ";
$suffixText .= ") AS Numbered WHERE Number BETWEEN " . ($offset + 1) . " AND " . ($offset + $limit) . " ORDER BY Number";
$nestedQuery = true;
// Otherwise a simple query
} else {
$text = "SELECT {$distinct}";
}
}
// Now add the columns to be selected
$text .= implode(", ", $sqlQuery->select);
}
$text .= " FROM " . implode(" ", $sqlQuery->from);
if ($sqlQuery->where) {
$text .= " WHERE (" . $sqlQuery->getFilter() . ")";
}
if ($sqlQuery->groupby) {
$text .= " GROUP BY " . implode(", ", $sqlQuery->groupby);
}
if ($sqlQuery->having) {
$text .= " HAVING ( " . implode(" ) AND ( ", $sqlQuery->having) . " )";
}
if (!$nestedQuery && $sqlQuery->orderby) {
$text .= " ORDER BY " . $sqlQuery->orderby;
}
// $suffixText is used by the nested queries to create an offset limit
if ($suffixText) {
$text .= $suffixText;
}
return $text;
}
/**
* Convert a SQLQuery object into a SQL statement
*/
public function sqlQueryToString(SQLQuery $sqlQuery)
{
$distinct = $sqlQuery->distinct ? "DISTINCT " : "";
if ($sqlQuery->delete) {
$text = "DELETE ";
} else {
if ($sqlQuery->select) {
$text = "SELECT {$distinct}" . implode(", ", $sqlQuery->select);
}
}
if ($sqlQuery->from) {
$text .= " FROM " . implode(" ", $sqlQuery->from);
}
if ($sqlQuery->where) {
$text .= " WHERE (" . $sqlQuery->getFilter() . ")";
}
if ($sqlQuery->groupby) {
$text .= " GROUP BY " . implode(", ", $sqlQuery->groupby);
}
if ($sqlQuery->having) {
$text .= " HAVING ( " . implode(" ) AND ( ", $sqlQuery->having) . " )";
}
if ($sqlQuery->orderby) {
$text .= " ORDER BY " . $sqlQuery->orderby;
}
if ($sqlQuery->limit) {
$limit = $sqlQuery->limit;
// Pass limit as array or SQL string value
if (is_array($limit)) {
if (!array_key_exists('limit', $limit)) {
throw new InvalidArgumentException('SQLQuery::limit(): Wrong format for $limit: ' . var_export($limit, true));
}
if (isset($limit['start']) && is_numeric($limit['start']) && isset($limit['limit']) && is_numeric($limit['limit'])) {
$combinedLimit = $limit['start'] ? "{$limit['limit']} OFFSET {$limit['start']}" : "{$limit['limit']}";
} elseif (isset($limit['limit']) && is_numeric($limit['limit'])) {
$combinedLimit = (int) $limit['limit'];
} else {
$combinedLimit = false;
}
if (!empty($combinedLimit)) {
$text .= " LIMIT " . $combinedLimit;
}
} else {
$text .= " LIMIT " . $sqlQuery->limit;
}
}
return $text;
}
/**
* Changes any SELECT query thats not filtering on an ID
* to limit by the current language defined in {@link get_current_locale()}.
* It falls back to "Locale='' OR Lang IS NULL" and assumes that
* this implies querying for the default language.
*
* Use {@link disable_locale_filter()} to temporarily disable this "auto-filtering".
*/
function augmentSQL(SQLQuery &$query)
{
// If the record is saved (and not a singleton), and has a locale,
// limit the current call to its locale. This fixes a lot of problems
// with other extensions like Versioned
$locale = $this->owner->ID && $this->owner->Locale ? $this->owner->Locale : Translatable::get_current_locale();
$baseTable = ClassInfo::baseDataClass($this->owner->class);
$where = $query->where;
if ($locale && self::locale_filter_enabled() && !$query->filtersOnID() && array_search($baseTable, array_keys($query->from)) !== false && !preg_match('/("|\'|`)Locale("|\'|`)/', $query->getFilter())) {
$qry = sprintf('"%s"."Locale" = \'%s\'', $baseTable, Convert::raw2sql($locale));
$query->where[] = $qry;
}
}
/**
* Convert a SQLQuery object into a SQL statement
*/
public function sqlQueryToString(SQLQuery $sqlQuery)
{
if (!$sqlQuery->from) {
return '';
}
$distinct = $sqlQuery->distinct ? "DISTINCT " : "";
if ($sqlQuery->delete) {
$text = "DELETE ";
} else {
if ($sqlQuery->select) {
$text = "SELECT {$distinct}" . implode(", ", $sqlQuery->select);
}
}
if ($sqlQuery->from) {
$text .= " FROM " . implode(" ", $sqlQuery->from);
}
if ($sqlQuery->where) {
$text .= " WHERE (" . $sqlQuery->getFilter() . ")";
}
if ($sqlQuery->groupby) {
$text .= " GROUP BY " . implode(", ", $sqlQuery->groupby);
}
if ($sqlQuery->having) {
$text .= " HAVING ( " . implode(" ) AND ( ", $sqlQuery->having) . " )";
}
if ($sqlQuery->orderby) {
$text .= " ORDER BY " . $this->orderMoreSpecifically($sqlQuery->select, $sqlQuery->orderby);
}
if ($sqlQuery->limit) {
$limit = $sqlQuery->limit;
// Pass limit as array or SQL string value
if (is_array($limit)) {
if (!array_key_exists('limit', $limit)) {
user_error('SQLQuery::limit(): Wrong format for $limit', E_USER_ERROR);
}
if (isset($limit['start']) && is_numeric($limit['start']) && isset($limit['limit']) && is_numeric($limit['limit'])) {
$combinedLimit = "{$limit['limit']} OFFSET {$limit['start']}";
} elseif (isset($limit['limit']) && is_numeric($limit['limit'])) {
$combinedLimit = (int) $limit['limit'];
} else {
$combinedLimit = false;
}
if (!empty($combinedLimit)) {
$text .= " LIMIT " . $combinedLimit;
}
} else {
$text .= " LIMIT " . $sqlQuery->limit;
}
}
return $text;
}
