PHP DataQuery::getFinalisedQuery方法代碼示例

 /**
  * @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);
//.........這裏部分代碼省略.........

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