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