/**
* @return \SQLQuery
*/
public function getQuery()
{
if (!$this->queryCache) {
if ($this->dataClass) {
$dataQuery = new \DataQuery($this->dataClass);
if ($this->stage) {
$dataQuery->setQueryParam('Versioned.mode', 'stage');
$dataQuery->setQueryParam('Versioned.stage', $this->stage);
}
$this->queryCache = $dataQuery->getFinalisedQuery();
} else {
$this->queryCache = new \SQLQuery();
}
if (is_array($this->queryModifiers)) {
foreach ($this->queryModifiers as $queryModifier) {
if ($queryModifier instanceof QueryModifierInterface) {
$queryModifier->modify($this->queryCache, $this->data, $this);
} elseif (is_callable($queryModifier)) {
$queryModifier($this->queryCache, $this->data, $this);
}
}
}
}
return $this->queryCache;
}
/**
* Removes the result of query from this query.
*
* @param DataQuery $subtractQuery
* @param string $field
*/
public function subtract(DataQuery $subtractQuery, $field = 'ID')
{
$fieldExpression = $subtractQuery->expressionForField($field);
$subSelect = $subtractQuery->getFinalisedQuery();
$subSelect->setSelect(array());
$subSelect->selectField($fieldExpression, $field);
$subSelect->setOrderBy(null);
$this->where($this->expressionForField($field) . ' NOT IN (' . $subSelect->sql() . ')');
return $this;
}
/**
* Removes the result of query from this query.
*
* @param DataQuery $subtractQuery
* @param string $field
*/
public function subtract(DataQuery $subtractQuery, $field = 'ID')
{
$fieldExpression = $subtractQuery->expressionForField($field);
$subSelect = $subtractQuery->getFinalisedQuery();
$subSelect->setSelect(array());
$subSelect->selectField($fieldExpression, $field);
$subSelect->setOrderBy(null);
$subSelectSQL = $subSelect->sql($subSelectParameters);
$this->where(array($this->expressionForField($field) . " NOT IN ({$subSelectSQL})" => $subSelectParameters));
return $this;
}
/**
* Removes the result of query from this query.
*
* @param DataQuery $subtractQuery
* @param string $field
*/
public function subtract(DataQuery $subtractQuery, $field='ID') {
$subSelect= $subtractQuery->getFinalisedQuery();
$subSelect->select($this->expressionForField($field, $subSelect));
$this->where($this->expressionForField($field, $this).' NOT IN ('.$subSelect->sql().')');
}
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);
}
/**
* Tests that getFinalisedQuery can include all tables
*/
public function testConditionsIncludeTables()
{
// Including filter on parent table only doesn't pull in second
$query = new DataQuery('DataQueryTest_C');
$query->sort('"SortOrder"');
$query->where(array('"DataQueryTest_C"."Title" = ?' => array('First')));
$result = $query->getFinalisedQuery(array('Title'));
$from = $result->getFrom();
$this->assertContains('DataQueryTest_C', array_keys($from));
$this->assertNotContains('DataQueryTest_E', array_keys($from));
// Including filter on sub-table requires it
$query = new DataQuery('DataQueryTest_C');
$query->sort('"SortOrder"');
$query->where(array('"DataQueryTest_C"."Title" = ? OR "DataQueryTest_E"."SortOrder" > ?' => array('First', 2)));
$result = $query->getFinalisedQuery(array('Title'));
$from = $result->getFrom();
// Check that including "SortOrder" prompted inclusion of DataQueryTest_E table
$this->assertContains('DataQueryTest_C', array_keys($from));
$this->assertContains('DataQueryTest_E', array_keys($from));
$arrayResult = iterator_to_array($result->execute());
$first = array_shift($arrayResult);
$this->assertNotNull($first);
$this->assertEquals('First', $first['Title']);
$second = array_shift($arrayResult);
$this->assertNotNull($second);
$this->assertEquals('Last', $second['Title']);
$this->assertEmpty(array_shift($arrayResult));
}
