/**
* Set the ORDER BY clause of this query
*
* @see SQLQuery::orderby()
*
* @param String $sort Column to sort on (escaped SQL statement)
* @param String $direction Direction ("ASC" or "DESC", escaped SQL statement)
* @param Boolean $clear Clear existing values
* @return DataQuery
*/
public function sort($sort = null, $direction = null, $clear = true)
{
if ($clear) {
$this->query->setOrderBy($sort, $direction);
} else {
$this->query->addOrderBy($sort, $direction);
}
return $this;
}
private function checkBlogEntryPermissions()
{
$authorsId = array();
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('SiteTree_versions');
$sqlQuery->selectField('AuthorID');
$sqlQuery->addWhere('RecordID = ' . $this->ID);
$sqlQuery->setOrderBy('ID DESC');
$rawSQL = $sqlQuery->sql();
$result = $sqlQuery->execute();
foreach ($result as $row) {
$authorsId[] = $row['AuthorID'];
}
$sqlQuery->setDelete(true);
if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) {
return true;
} else {
return false;
}
}
function exportFoundationMembers()
{
$params = $this->owner->getRequest()->getVars();
if (!isset($params['fields']) || empty($params['fields'])) {
return $this->owner->httpError('412', 'missing required param fields');
}
if (!isset($params['ext']) || empty($params['ext'])) {
return $this->owner->httpError('412', 'missing required param ext');
}
$fields = $params['fields'];
$ext = $params['ext'];
$sanitized_fields = array();
if (!count($fields)) {
return $this->owner->httpError('412', 'missing required param fields');
}
$allowed_fields = array('ID' => 'ID', 'FirstName' => 'FirstName', 'SurName' => 'SurName', 'Email' => 'Email');
for ($i = 0; $i < count($fields); $i++) {
if (!array_key_exists($fields[$i], $allowed_fields)) {
return $this->httpError('412', 'invalid field');
}
array_push($sanitized_fields, 'Member.' . $fields[$i]);
}
$query = new SQLQuery();
$query->setFrom('Member');
$query->setSelect($sanitized_fields);
$query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID');
$query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code='foundation-members'");
$query->setOrderBy('SurName,FirstName');
$result = $query->execute();
$data = array();
foreach ($result as $row) {
$member = array();
foreach ($fields as $field) {
$member[$field] = $row[$field];
}
array_push($data, $member);
}
$filename = "Foundation_Members_" . date('Ymd') . "." . $ext;
return CSVExporter::getInstance()->export($filename, $data, ',');
}
/**
* Test that multiple order elements are maintained in the given order
*/
public function testOrderByMultiple()
{
if (DB::getConn() instanceof MySQLDatabase) {
$query = new SQLQuery();
$query->setSelect(array('"Name"', '"Meta"'));
$query->setFrom('"SQLQueryTest_DO"');
$query->setOrderBy(array('MID("Name", 8, 1) DESC', '"Name" ASC'));
$records = array();
foreach ($query->execute() as $record) {
$records[] = $record;
}
$this->assertCount(2, $records);
$this->assertEquals('Object 2', $records[0]['Name']);
$this->assertEquals('2', $records[0]['_SortColumn0']);
$this->assertEquals('Object 1', $records[1]['Name']);
$this->assertEquals('1', $records[1]['_SortColumn0']);
}
}
public function testJoinSubSelect()
{
$query = new SQLQuery();
$query->setFrom('MyTable');
$query->addInnerJoin('(SELECT * FROM MyOtherTable)', 'Mot.MyTableID = MyTable.ID', 'Mot');
$query->addLeftJoin('(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID)', 'Mlt.MyOtherTableID = Mot.ID', 'Mlt');
$query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC');
$this->assertSQLEquals('SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' . 'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' . 'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' . 'ORDER BY "_SortColumn0" DESC', $query->sql($parameters));
}
public function testReverseOrderBy()
{
$query = new SQLQuery();
$query->setFrom('MyTable');
// default is ASC
$query->setOrderBy("Name");
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name DESC', $query->sql());
$query->setOrderBy("Name DESC");
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name ASC', $query->sql());
$query->setOrderBy(array("Name" => "ASC"));
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name DESC', $query->sql());
$query->setOrderBy(array("Name" => 'DESC', 'Color' => 'asc'));
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name ASC, Color DESC', $query->sql());
$query->setOrderBy('implode("MyName","Color") DESC');
$query->reverseOrderBy();
$this->assertEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql());
}
/**
* Retrieves Locations by lat, long, distance, and optionally a limit.
*/
public function getLocationSQLResultsByLatLong($lat = 37, $long = -122, $distance = 25, $limit = null)
{
//$data = DB::query('SELECT "ID" FROM "Marker" LIMIT 0 , '.$limit.';')->value();
//$query = 'SELECT "ID", ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('.$long.') ) + sin( radians('.$lat.') ) * sin( radians( Latitude ) ) ) ) AS "Distance" FROM "Marker" HAVING "Distance" < '.$distance.' ORDER BY "Distance" LIMIT 0 , '.$limit.';';
$markerClass = StoreFinder::$MarkerClass;
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom($markerClass);
$sqlQuery->selectField('*');
$sqlQuery->selectField('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat . ') ) * sin( radians( Latitude ) ) ) )', 'Distance');
$sqlQuery->setHaving("Distance < " . $distance);
$sqlQuery->setOrderBy('Distance');
$sqlQuery->setLimit($limit);
if ($markerClass != 'Marker') {
$sqlQuery->addLeftJoin("Marker", 'Marker.ID = ' . $markerClass . '.ID');
}
$this->extraSQL($sqlQuery);
// Execute and return a Query object
$result = $sqlQuery->execute();
return $result;
}
function exportUsersPerRegion()
{
$params = $this->owner->getRequest()->getVars();
if (!isset($params['countries']) || empty($params['countries'])) {
return $this->owner->httpError('412', 'missing required param countries');
}
if (!isset($params['members']) || empty($params['members'])) {
return $this->owner->httpError('412', 'missing required param members');
}
$countries = $params['countries'];
$members = $params['members'];
$join_members = '';
$join_countries = '';
if (!count($countries)) {
return $this->owner->httpError('412', 'missing required param countries');
} else {
foreach ($countries as $country) {
$join_countries .= "'" . $country . "',";
}
}
$join_countries = rtrim($join_countries, ",");
if (!count($members)) {
return $this->owner->httpError('412', 'missing required param members');
} else {
foreach ($members as $member) {
$join_members .= "'" . $member . "',";
}
}
$join_members = rtrim($join_members, ",");
$query = new SQLQuery();
$select_fields = array('Member.FirstName', 'Member.Surname', 'Member.Email', 'Member.City', 'Member.State', 'Member.Country');
$query->setFrom('Member');
$query->setSelect($select_fields);
$query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID');
$query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code IN (" . $join_members . ")");
$query->setWhere("Member.Country IN (" . $join_countries . ")");
$query->setOrderBy('SurName,FirstName');
$result = $query->execute();
$data = array();
foreach ($result as $row) {
$member = array('FirstName' => $row['FirstName'], 'Surname' => $row['Surname'], 'Email' => $row['Email'], 'City' => $row['City'], 'State' => $row['State'], 'Country' => CountryCodes::$iso_3166_countryCodes[$row['Country']]);
array_push($data, $member);
}
$filename = "UsersPerCountry" . date('Ymd') . ".csv";
return CSVExporter::getInstance()->export($filename, $data, ',');
}
/**
* 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()) {
foreach ($orderby as $k => $dir) {
// don't touch functions in the ORDER BY or 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]}\"";
// remove original sort
unset($orderby[$k]);
// add new columns sort
$orderby[$qualCol] = $dir;
} else {
$qualCol = "\"{$parts['0']}\"";
}
// 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) . '"';
// 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()
if (!in_array($qualCol, $query->getSelect())) {
$query->selectField($qualCol);
}
}
}
$query->setOrderBy($orderby);
}
}
/**
* @return DataList
*/
private function getAllowedDependants()
{
$steps_query = new SQLQuery();
$steps_query->setSelect("ID");
$steps_query->setFrom("SurveyStepTemplate");
$high_order = $this->Step()->order();
$current_survey_id = $this->Step()->SurveyTemplateID;
$steps_query->setWhere("SurveyTemplateID = {$current_survey_id} AND `Order` <= {$high_order} ");
$steps_query->setOrderBy('`Order`', 'ASC');
$current_step_ids = $steps_query->execute()->keyedColumn();
return SurveyQuestionTemplate::get()->filter(array('StepID' => $current_step_ids));
}
/**
* @return SQLQuery
*/
protected function getRemoteObjectsQuery()
{
// Get all tables to query
$tables = $this->getRemoteClassHierarchy();
$baseClass = array_shift($tables);
// Generate sql query
$query = new SQLQuery("\"{$baseClass}\".*", "\"{$baseClass}\"", $this->targetWhere);
$query->setOrderBy("\"{$baseClass}\".\"ID\" ASC");
// Sort by ID for some performance reasons
foreach ($tables as $class) {
$query->addSelect("\"{$class}\".*");
$query->addLeftJoin($class, "\"{$baseClass}\".\"ID\" = \"{$class}\".\"ID\"");
}
return $query;
}
