/** * Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed * in the search. * * @return Array */ public function pagesIncluded() { $sng = singleton('SiteTree'); $ids = array(); $query = new DataQuery('SiteTree'); $query->setQueriedColumns(array('ID', 'ParentID')); foreach ($this->params as $name => $val) { $SQL_val = Convert::raw2sql($val); switch ($name) { case 'Term': $query->whereAny(array("\"URLSegment\" LIKE '%{$SQL_val}%'", "\"Title\" LIKE '%{$SQL_val}%'", "\"MenuTitle\" LIKE '%{$SQL_val}%'", "\"Content\" LIKE '%{$SQL_val}%'")); break; case 'LastEditedFrom': $fromDate = new DateField(null, null, $SQL_val); $query->where("\"LastEdited\" >= '{$fromDate->dataValue()}'"); break; case 'LastEditedTo': $toDate = new DateField(null, null, $SQL_val); $query->where("\"LastEdited\" <= '{$toDate->dataValue()}'"); break; case 'ClassName': if ($val && $val != 'All') { $query->where("\"ClassName\" = '{$SQL_val}'"); } break; default: if (!empty($val) && $sng->hasDatabaseField($name)) { $filter = $sng->dbObject($name)->defaultSearchFilter(); $filter->setValue($val); $filter->apply($query); } } } foreach ($query->execute() as $row) { $ids[] = array('ID' => $row['ID'], 'ParentID' => $row['ParentID']); } return $ids; }
public function getDataObjects() { Versioned::reading_stage('Stage'); $rows = array(); $tables = $this->getQueryTables(); $allFields = $this->getReportableFields(); $selectedFields = $this->ReportFields->getValues(); $fields = array(); $sum = $this->SumFields->getValues(); $sum = $sum ? $sum : array(); if ($selectedFields) { foreach ($selectedFields as $field) { if (!isset($allFields[$field])) { continue; } $as = $this->dottedFieldToUnique($allFields[$field]); $fields[$as] = $field; } } $baseTable = null; // stores the relationship name-to-aliasname mapping $relatedTables = array(); // stores the tbl_{one}_{two} alias name-to-class-type mapping $aliasToDataType = array(); foreach ($tables as $typeName => $alias) { if (strpos($typeName, '.')) { $relatedTables[$typeName] = $alias; } else { $baseTable = $typeName; } } if (!$baseTable) { throw new Exception("All freeform reports must have a base data type selected"); } $multiValue = array(); // go through and capture all the multivalue fields // at the same time, remap Type.Field structures to // TableName.Field $remappedFields = array(); $simpleFields = array(); foreach ($fields as $alias => $name) { $class = ''; if (strpos($name, '.')) { list($class, $field) = explode('.', $name); } else { $class = $baseTable; $field = $name; } $typeFields = array(); if (class_exists($class)) { $instance = singleton($class); $typeFields = method_exists($instance, 'getAdvancedReportableFields') ? $instance->getAdvancedReportableFields() : array(); } else { if ($dataType = $this->fieldAliasToDataType($class)) { $instance = singleton($dataType); $typeFields = method_exists($instance, 'getAdvancedReportableFields') ? $instance->getAdvancedReportableFields($class . '.') : array(); } } $fieldAlias = ''; // if the name is prefixed, we need to figure out what the actual $class is from the // remote join if (strpos($name, 'tbl_') === 0) { $fieldAlias = $this->dottedFieldToUnique($name); $selectField = '"' . Convert::raw2sql($class) . '"."' . Convert::raw2sql($field) . '"'; if (isset($typeFields[$field])) { $selectField = $typeFields[$field]; } $remappedFields[$fieldAlias] = $selectField; if (in_array($name, $sum)) { $remappedFields[$fieldAlias] = 'SUM(' . $remappedFields[$fieldAlias] . ')'; } } else { if (isset($typeFields[$name])) { $remappedFields[$name] = $typeFields[$name]; } else { if (in_array($name, $sum)) { $remappedFields[$field] = 'SUM(' . $field . ')'; } else { // just store it as is $simpleFields[$alias] = $field; } } } $field = preg_replace('/Value$/', '', $field); $db = Config::inst()->get($class, 'db'); if (isset($db[$field]) && $db[$field] == 'MultiValueField') { $multiValue[] = $alias; } } $dataQuery = new DataQuery($baseTable); $dataQuery->setQueriedColumns($simpleFields); // converts all the fields being queried into the appropriate // tables for querying. $query = $dataQuery->getFinalisedQuery(); // explicit fields that we want to query against, that come from joins. // we need to do it this way to ensure that a) the field names in the results match up to // the header labels specified and b) because dataQuery by default doesn't return fields from // joined tables, it merely allows for fields from the base dataClass foreach ($remappedFields as $alias => $name) { $query->selectField($name, $alias); } // and here's where those joins are added. This is somewhat copied from dataQuery, // but modified so that our table aliases are used properly to avoid the bug described at // https://github.com/silverstripe/silverstripe-framework/issues/3518 // it also ensures the alias names are in a format that our header assignment and field retrieval works as // expected foreach (array_keys($relatedTables) as $relation) { $this->applyRelation($baseTable, $query, $relation); } $sort = $this->getSort(); $query->setOrderBy($sort); $filter = $this->getConditions(); $where = $this->getWhereClause($filter, $baseTable); $query->setWhere($where); $groupBy = $this->GroupBy->getValues(); if (count($groupBy)) { $query->setGroupBy($groupBy); } $sql = $query->sql(); $out = $query->execute(); $rows = array(); $headers = $this->getHeaders(); foreach ($out as $row) { foreach ($multiValue as $field) { $row[$field] = implode("\n", (array) unserialize($row[$field])); } $rows[] = $row; } return ArrayList::create($rows); }