/** * Return the {@link SQLQuery} that provides your report data. */ function sourceQuery($params) { $sqlQuery = new SQLQuery(); $sqlQuery->setFrom('CalendarEvent'); $sqlQuery->selectField('Date'); $sqlQuery->selectField('CalendarEvent.Title', 'Event'); $sqlQuery->selectField('StartTime'); $sqlQuery->selectField('EndTime'); $sqlQuery->addInnerJoin('CalendarEventDate', '"CalendarEventDate"."CalendarEventID" = "CalendarEvent"."ID"'); if (isset($params['DateFrom'])) { $fromDate = new SS_DateTime('FromDate'); $fromDate->setValue($params['DateFrom']); $sqlQuery->addWhere(array('Date >= ?' => $fromDate->Format("Y-m-d"))); } if (isset($params['DateTo'])) { $toDate = new SS_DateTime('ToDate'); $toDate->setValue($params['DateTo']); $sqlQuery->addWhere(array('Date <= ?' => $toDate->Format("Y-m-d"))); } if (isset($params['PrivateBookings'])) { if ($params['PrivateBookings'] == 'Private') { $sqlQuery->addWhere('Private = 1'); } elseif ($params['PrivateBookings'] == 'Public') { $sqlQuery->addWhere('Private = 0'); } } $sqlQuery->addOrderBy('Date'); $sqlQuery->addOrderBy('Event'); $sqlQuery->addOrderBy('StartTime'); $sqlQuery->addOrderBy('EndTime'); return $sqlQuery; }
protected function getRemoteObjectsQuery() { // Do something really lazy here; Join on all tables to do the mapping really sneakily $query = new SQLQuery('"' . $this->tableName . '"."ID"'); $query->setFrom('"' . $this->tableName . '"'); // relations are add-only, so just get unimported relations $query->setWhere('"' . $this->tableName . '"."_ImportedID" = 0'); foreach ($this->fields as $field => $class) { // Join table $query->addInnerJoin($class, "\"{$class}\".\"ID\" = \"{$this->tableName}\".\"{$field}\""); // Remove unmapped related tables $query->addWhere("\"{$class}\".\"_ImportedID\" > 0"); // Substitute imported ID from related class for that ID $query->selectField("\"{$class}\".\"_ImportedID\"", $field); } return $query; }
public function augmentSQL(SQLQuery &$query) { if (Config::inst()->forClass('Post')->allow_reading_spam) { return; } $member = Member::currentUser(); $forum = $this->owner->Forum(); // Do Status filtering if ($member && is_numeric($forum->ID) && $member->ID == $forum->Moderator()->ID) { $filter = "\"Post\".\"Status\" IN ('Moderated', 'Awaiting')"; } else { $filter = "\"Post\".\"Status\" = 'Moderated'"; } $query->addWhere($filter); // Filter out posts where the author is in some sort of banned / suspended status $query->addInnerJoin("Member", "\"AuthorStatusCheck\".\"ID\" = \"Post\".\"AuthorID\"", "AuthorStatusCheck"); $authorStatusFilter = array(array('"AuthorStatusCheck"."ForumStatus"' => 'Normal')); if ($member && $member->ForumStatus === 'Ghost') { $authorStatusFilter[] = array('"Post"."AuthorID" = ?', $member->ID); } $query->addWhereAny($authorStatusFilter); $query->setDistinct(false); }
function exportCompanyData() { $params = $this->owner->getRequest()->getVars(); if (!isset($params['report_name']) || empty($params['report_name']) || !count($params['report_name'])) { return $this->owner->httpError('412', 'missing required param report_name'); } if (!isset($params['extension']) || empty($params['extension'])) { return $this->owner->httpError('412', 'missing required param extension'); } $report_name = isset($params['report_name']) ? $params['report_name'] : ''; $fields = isset($params['fields']) ? $params['fields'] : array(); $ext = $params['extension']; $query = new SQLQuery(); if ($report_name) { switch ($report_name) { case 'sponsorship_type': $query->setFrom('Company'); $query->addLeftJoin('SummitSponsorPage_Companies', 'SummitSponsorPage_Companies.CompanyID = Company.ID'); $query->addLeftJoin('Summit', 'Summit.ID = SummitSponsorPage_Companies.SummitID'); $query->addWhere('Summit.Active', '1'); $fields = array_merge($fields, array('Sponsorship' => 'SummitSponsorPage_Companies.SponsorshipType', 'Summit ID' => 'Summit.ID')); $query->setSelect($fields); $query->addOrderBy('SummitSponsorPage_Companies.SponsorshipType'); $filename = "Sponsorship_Levels_" . date('Ymd') . "." . $ext; break; case 'member_level': $query->setFrom('Company'); array_push($fields, 'Company.MemberLevel'); $query->setSelect($fields); $filename = "Foundation_Levels_" . date('Ymd') . "." . $ext; break; case 'users_roles': $query->setFrom('Company'); $query->addInnerJoin('Company_Administrators', 'Company_Administrators.CompanyID = Company.ID'); $query->addLeftJoin('Member', 'Member.ID = Company_Administrators.MemberID'); $query->addLeftJoin('Group', 'Group.ID = Company_Administrators.GroupID'); array_push($fields, 'Group.Title'); $query->setSelect($fields); $query->addOrderBy('Company.Name'); $filename = "User_Roles_" . date('Ymd') . "." . $ext; break; case 'affiliates': $query->setFrom('Org'); $query->addLeftJoin('Affiliation', 'Affiliation.OrganizationID = Org.ID'); $query->addLeftJoin('Member', 'Member.ID = Affiliation.MemberID'); $fields = array_merge($fields, array('Is Current' => 'Affiliation.Current', 'Job Title' => 'Affiliation.JobTitle')); $query->setSelect($fields); $query->addOrderBy('Org.Name'); $filename = "Employees_Affiliates_" . date('Ymd') . "." . $ext; break; case 'deployments': $query->setFrom('Org'); $query->addInnerJoin('Deployment', 'Deployment.OrgID = Org.ID'); $custom_fields = array('Creation' => 'Deployment.Created', 'Edited' => 'Deployment.LastEdited', 'Label' => 'Deployment.Label', 'Is Public' => 'Deployment.IsPublic'); $fields = array_merge($fields, $custom_fields); $query->setSelect($fields); $query->selectField("CONCAT('http://openstack.org/sangria/DeploymentDetails/',Deployment.ID)", "Link"); $query->addOrderBy('Org.Name'); $filename = "Deployments_" . date('Ymd') . "." . $ext; break; case 'deployment_surveys': $query->setFrom('Org'); $query->addLeftJoin('DeploymentSurvey', 'DeploymentSurvey.OrgID = Org.ID'); $query->addLeftJoin('Member', 'DeploymentSurvey.MemberID = Member.ID'); $custom_fields = array('Creation' => 'DeploymentSurvey.Created', 'Edited' => 'DeploymentSurvey.LastEdited', 'Title' => 'DeploymentSurvey.Title', 'City' => 'DeploymentSurvey.PrimaryCity', 'State' => 'DeploymentSurvey.PrimaryState', 'Country' => 'DeploymentSurvey.PrimaryCountry', 'Org Size' => 'DeploymentSurvey.OrgSize', 'Is Group Member' => 'DeploymentSurvey.UserGroupMember', 'Group Name' => 'DeploymentSurvey.UserGroupName', 'Ok to Contact' => 'DeploymentSurvey.OkToContact'); //insert custom fields after org fields $pos = -1; foreach ($fields as $field) { $pos++; if (strpos($field, 'Org') !== false) { continue; } else { array_splice($fields, $pos, 0, $custom_fields); break; } } $query->setSelect($fields); $query->selectField("CONCAT('http://openstack.org/sangria/SurveyDetails/',DeploymentSurvey.ID)", "Link"); $filename = "Deployment_Surveys" . date('Ymd') . "." . $ext; break; case 'speakers': $query->setFrom('PresentationSpeaker'); $query->addLeftJoin('Affiliation', 'Affiliation.MemberID = PresentationSpeaker.MemberID'); $query->addLeftJoin('Org', 'Affiliation.OrganizationID = Org.ID'); $query->addLeftJoin('Summit', 'Summit.ID = PresentationSpeaker.SummitID'); $custom_fields = array('Speaker Name' => 'PresentationSpeaker.FirstName', 'Speaker Surname' => 'PresentationSpeaker.LastName', 'Summit' => 'Summit.Name'); $fields = array_merge($fields, $custom_fields); $query->setSelect($fields); $filename = "Speakers_" . date('Ymd') . "." . $ext; break; } } //die($query->sql()); $result = $query->execute(); $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $result, $delimiter); }
/** * Traverse the relationship fields, and add the table * mappings to the query object state. This has to be called * in any overloaded {@link SearchFilter->apply()} methods manually. * * @param String|array $relation The array/dot-syntax relation to follow * @return The model class of the related item */ public function applyRelation($relation) { // NO-OP if (!$relation) { return $this->dataClass; } if (is_string($relation)) { $relation = explode(".", $relation); } $modelClass = $this->dataClass; foreach ($relation as $rel) { $model = singleton($modelClass); if ($component = $model->has_one($rel)) { if (!$this->query->isJoinedTo($component)) { $foreignKey = $rel; $realModelClass = ClassInfo::table_for_object_field($modelClass, "{$foreignKey}ID"); $this->query->addLeftJoin($component, "\"{$component}\".\"ID\" = \"{$realModelClass}\".\"{$foreignKey}ID\""); /** * add join clause to the component's ancestry classes so that the search filter could search on * its ancestor fields. */ $ancestry = ClassInfo::ancestry($component, true); if (!empty($ancestry)) { $ancestry = array_reverse($ancestry); foreach ($ancestry as $ancestor) { if ($ancestor != $component) { $this->query->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\""); } } } } $modelClass = $component; } elseif ($component = $model->has_many($rel)) { if (!$this->query->isJoinedTo($component)) { $ancestry = $model->getClassAncestry(); $foreignKey = $model->getRemoteJoinField($rel); $this->query->addLeftJoin($component, "\"{$component}\".\"{$foreignKey}\" = \"{$ancestry[0]}\".\"ID\""); /** * add join clause to the component's ancestry classes so that the search filter could search on * its ancestor fields. */ $ancestry = ClassInfo::ancestry($component, true); if (!empty($ancestry)) { $ancestry = array_reverse($ancestry); foreach ($ancestry as $ancestor) { if ($ancestor != $component) { $this->query->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\""); } } } } $modelClass = $component; } elseif ($component = $model->many_many($rel)) { list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component; $parentBaseClass = ClassInfo::baseDataClass($parentClass); $componentBaseClass = ClassInfo::baseDataClass($componentClass); $this->query->addInnerJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\""); $this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\""); if (ClassInfo::hasTable($componentClass)) { $this->query->addLeftJoin($componentClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentClass}\".\"ID\""); } $modelClass = $componentClass; } } return $modelClass; }
public function testJoinSubSelect() { // Test sub-select works $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->assertEquals('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()); // Test that table names do not get mistakenly identified as sub-selects $query = new SQLQuery(); $query->setFrom('"MyTable"'); $query->addInnerJoin('NewsArticleSelected', '"News"."MyTableID" = "MyTable"."ID"', 'News'); $this->assertEquals('SELECT * FROM "MyTable" INNER JOIN "NewsArticleSelected" AS "News" ON ' . '"News"."MyTableID" = "MyTable"."ID"', $query->sql()); }
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 testInnerJoin() { $query = new SQLQuery(); $query->setFrom('MyTable'); $query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2'); $query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID'); $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql()); $query = new SQLQuery(); $query->setFrom('MyTable'); $query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1'); $query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2'); $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql()); }
/** * Given a class, object id, set of stateful ids and a list of changed fields (in a special format), * return what statefulids need updating in this index * * Internal function used by SearchUpdater. * * @param $class * @param $id * @param $statefulids * @param $fields * @return array */ public function getDirtyIDs($class, $id, $statefulids, $fields) { $dirty = array(); // First, if this object is directly contained in the index, add it foreach ($this->classes as $searchclass => $options) { if ($searchclass == $class || $options['include_children'] && is_subclass_of($class, $searchclass)) { $base = ClassInfo::baseDataClass($searchclass); $dirty[$base] = array(); foreach ($statefulids as $statefulid) { $key = serialize($statefulid); $dirty[$base][$key] = $statefulid; } } } $current = SearchVariant::current_state(); // Then, for every derived field foreach ($this->getDerivedFields() as $derivation) { // If the this object is a subclass of any of the classes we want a field from if (!SearchIntrospection::is_subclass_of($class, $derivation['classes'])) { continue; } if (!array_intersect_key($fields, $derivation['fields'])) { continue; } foreach (SearchVariant::reindex_states($class, false) as $state) { SearchVariant::activate_state($state); $ids = array($id); foreach ($derivation['chain'] as $step) { if ($step['through'] == 'has_one') { $sql = new SQLQuery('"ID"', '"' . $step['class'] . '"', '"' . $step['foreignkey'] . '" IN (' . implode(',', $ids) . ')'); singleton($step['class'])->extend('augmentSQL', $sql); $ids = $sql->execute()->column(); } else { if ($step['through'] == 'has_many') { $sql = new SQLQuery('"' . $step['class'] . '"."ID"', '"' . $step['class'] . '"', '"' . $step['otherclass'] . '"."ID" IN (' . implode(',', $ids) . ')'); $sql->addInnerJoin($step['otherclass'], '"' . $step['class'] . '"."ID" = "' . $step['otherclass'] . '"."' . $step['foreignkey'] . '"'); singleton($step['class'])->extend('augmentSQL', $sql); $ids = $sql->execute()->column(); } } } SearchVariant::activate_state($current); if ($ids) { $base = $derivation['base']; if (!isset($dirty[$base])) { $dirty[$base] = array(); } foreach ($ids as $id) { $statefulid = array('id' => $id, 'state' => $state); $key = serialize($statefulid); $dirty[$base][$key] = $statefulid; } } } } return $dirty; }
public function testParameterisedInnerJoins() { $query = new SQLQuery(); $query->setSelect(array('"SQLQueryTest_DO"."Name"', '"SubSelect"."Count"')); $query->setFrom('"SQLQueryTest_DO"'); $query->addInnerJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLQueryTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLQueryTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%')); $query->addWhere(array('"SQLQueryTest_DO"."Date" > ?' => '2012-08-08 12:00')); $this->assertSQLEquals('SELECT "SQLQueryTest_DO"."Name", "SubSelect"."Count" FROM "SQLQueryTest_DO" INNER JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLQueryTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLQueryTest_DO"."Name" = "SubSelect"."Title" WHERE ("SQLQueryTest_DO"."Date" > ?)', $query->sql($parameters)); $this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters); $query->execute(); }
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, ','); }
/** * Augment the the SQLQuery that is created by the DataQuery * @todo Should this all go into VersionedDataQuery? */ function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { $baseTable = ClassInfo::baseDataClass($dataQuery->dataClass()); switch ($dataQuery->getQueryParam('Versioned.mode')) { // Noop case '': break; // Reading a specific data from the archive // Reading a specific data from the archive case 'archive': $date = $dataQuery->getQueryParam('Versioned.date'); foreach ($query->getFrom() as $table => $dummy) { $query->renameTable($table, $table . '_versions'); $query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\""); // Add all <basetable>_versions columns foreach (self::$db_for_versions_table as $name => $type) { $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name); } $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID"); if ($table != $baseTable) { $query->addFrom(array($table => " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"")); } } // Link to the version archived on that date $archiveTable = $this->requireArchiveTempTable($baseTable, $date); $query->addFrom(array($archiveTable => "INNER JOIN \"{$archiveTable}\"\n\t\t\t\tON \"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"")); break; // Reading a specific stage (Stage or Live) // Reading a specific stage (Stage or Live) case 'stage': $stage = $dataQuery->getQueryParam('Versioned.stage'); if ($stage && $stage != $this->defaultStage) { foreach ($query->getFrom() as $table => $dummy) { // Only rewrite table names that are actually part of the subclass tree // This helps prevent rewriting of other tables that get joined in, in // particular, many_many tables if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) { $query->renameTable($table, $table . '_' . $stage); } } } break; // Return all version instances // Return all version instances case 'all_versions': case 'latest_versions': foreach ($query->getFrom() as $alias => $join) { if ($alias != $baseTable) { $query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""); } $query->renameTable($alias, $alias . '_versions'); } // Add all <basetable>_versions columns foreach (self::$db_for_versions_table as $name => $type) { $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name); } $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID"); // latest_version has one more step // Return latest version instances, regardless of whether they are on a particular stage // This provides "show all, including deleted" functonality if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') { $archiveTable = self::requireArchiveTempTable($baseTable); $query->addInnerJoin($archiveTable, "\"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""); } break; default: throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode')); } }