/**
* Ensure that if a query has an order by clause, those columns are present in the select.
*
* @param SQLQuery $query
* @return null
*/
protected function ensureSelectContainsOrderbyColumns($query, $originalSelect = array())
{
$tableClasses = ClassInfo::dataClassesFor($this->dataClass);
$baseClass = array_shift($tableClasses);
if ($orderby = $query->getOrderBy()) {
$newOrderby = array();
$i = 0;
foreach ($orderby as $k => $dir) {
$newOrderby[$k] = $dir;
// don't touch functions in the ORDER BY or public function calls
// selected as fields
if (strpos($k, '(') !== false) {
continue;
}
$col = str_replace('"', '', trim($k));
$parts = explode('.', $col);
// Pull through SortColumn references from the originalSelect variables
if (preg_match('/_SortColumn/', $col)) {
if (isset($originalSelect[$col])) {
$query->selectField($originalSelect[$col], $col);
}
continue;
}
if (count($parts) == 1) {
$databaseFields = DataObject::database_fields($baseClass);
// database_fields() doesn't return ID, so we need to
// manually add it here
$databaseFields['ID'] = true;
if (isset($databaseFields[$parts[0]])) {
$qualCol = "\"{$baseClass}\".\"{$parts[0]}\"";
} else {
$qualCol = "\"{$parts['0']}\"";
}
// remove original sort
unset($newOrderby[$k]);
// add new columns sort
$newOrderby[$qualCol] = $dir;
// To-do: Remove this if block once SQLQuery::$select has been refactored to store getSelect()
// format internally; then this check can be part of selectField()
$selects = $query->getSelect();
if (!isset($selects[$col]) && !in_array($qualCol, $selects)) {
$query->selectField($qualCol);
}
} else {
$qualCol = '"' . implode('"."', $parts) . '"';
if (!in_array($qualCol, $query->getSelect())) {
unset($newOrderby[$k]);
$newOrderby["\"_SortColumn{$i}\""] = $dir;
$query->selectField($qualCol, "_SortColumn{$i}");
$i++;
}
}
}
$query->setOrderBy($newOrderby);
}
}
/**
* Ensure that if a query has an order by clause, those columns are present in the select.
*
* @param SQLQuery $query
* @return null
*/
protected function ensureSelectContainsOrderbyColumns($query) {
$tableClasses = ClassInfo::dataClassesFor($this->dataClass);
$baseClass = array_shift($tableClasses);
if($query->orderby) {
$orderby = $query->getOrderBy();
foreach($orderby as $k => $dir) {
// don't touch functions in the ORDER BY or function calls
// selected as fields
if(strpos($k, '(') !== false || preg_match('/_SortColumn/', $k))
continue;
$col = str_replace('"', '', trim($k));
$parts = explode('.', $col);
if(count($parts) == 1) {
$databaseFields = DataObject::database_fields($baseClass);
// database_fields() doesn't return ID, so we need to
// manually add it here
$databaseFields['ID'] = true;
if(isset($databaseFields[$parts[0]])) {
$qualCol = "\"$baseClass\".\"{$parts[0]}\"";
// remove original sort
unset($orderby[$k]);
// add new columns sort
$orderby[$qualCol] = $dir;
} else {
$qualCol = "\"$parts[0]\"";
}
if(!isset($query->select[$col]) && !in_array($qualCol, $query->select)) {
$query->select[] = $qualCol;
}
} else {
$qualCol = '"' . implode('"."', $parts) . '"';
if(!in_array($qualCol, $query->select)) {
$query->select[] = $qualCol;
}
}
}
$query->orderby = $orderby;
}
}
/**
* 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;
}
/**
* 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;
}
