PHP SQLSelect::addLeftJoin方法代码示例

 /**
  * Join table via many_many relationship
  *
  * @param string $parentClass
  * @param string $componentClass
  * @param string $parentField
  * @param string $componentField
  * @param string $relationTable Name of relation table
  */
 protected function joinManyManyRelationship($parentClass, $componentClass, $parentField, $componentField, $relationTable)
 {
     $parentBaseClass = ClassInfo::baseDataClass($parentClass);
     $componentBaseClass = ClassInfo::baseDataClass($componentClass);
     $this->query->addLeftJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
     $this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
     /**
      * add join clause to the component's ancestry classes so that the search filter could search on
      * its ancestor fields.
      */
     $ancestry = ClassInfo::ancestry($componentClass, true);
     $ancestry = array_reverse($ancestry);
     foreach ($ancestry as $ancestor) {
         if ($ancestor != $componentBaseClass) {
             $this->query->addInnerJoin($ancestor, "\"{$componentBaseClass}\".\"ID\" = \"{$ancestor}\".\"ID\"");
         }
     }
 }

    public function testParameterisedLeftJoins()
    {
        $query = new SQLSelect();
        $query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"'));
        $query->setFrom('"SQLSelectTest_DO"');
        $query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%'));
        $query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00'));
        $this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
			FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
		   GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
		   "SubSelect"."Title"
			WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters));
        $this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters);
        $query->execute();
    }

 /**
  * 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 = $model->getReverseAssociation($component);
                 $this->query->addLeftJoin($component, "\"{$component}\".\"ID\" = \"{$modelClass}\".\"{$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 testInnerJoin()
 {
     $query = new SQLSelect();
     $query->setFrom('MyTable');
     $query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
     $query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
     $this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql($parameters));
     $query = new SQLSelect();
     $query->setFrom('MyTable');
     $query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
     $query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
     $this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql($parameters));
 }

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
 {
     if (Subsite::$disable_subsite_filter) {
         return;
     }
     if (Cookie::get('noSubsiteFilter') == 'true') {
         return;
     }
     // If you're querying by ID, ignore the sub-site - this is a bit ugly...
     if (!$query->filtersOnID()) {
         /*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
         		else */
         $subsiteID = (int) Subsite::currentSubsiteID();
         // Don't filter by Group_Subsites if we've already done that
         $hasGroupSubsites = false;
         foreach ($query->getFrom() as $item) {
             if (is_array($item) && strpos($item['table'], 'Group_Subsites') !== false || !is_array($item) && strpos($item, 'Group_Subsites') !== false) {
                 $hasGroupSubsites = true;
                 break;
             }
         }
         if (!$hasGroupSubsites) {
             if ($subsiteID) {
                 $query->addLeftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\" \n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
                 $query->addWhere("(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)");
             } else {
                 $query->addWhere("\"Group\".\"AccessAllSubsites\" = 1");
             }
         }
         // WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
         $select = $query->getSelect();
         if (isset($select[0]) && !$select[0] == 'COUNT(*)') {
             $query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
         }
     }
 }

 /**
  *	Return all data object visible attributes of the specified type, with optional filters.
  *
  *	@parameter <{DATA_OBJECT_NAME}> string
  *	@parameter <{LIMIT}> integer
  *	@parameter <{SORT}> array(string, string)
  *	@parameter <{FILTERS}> array
  *	@return array
  */
 public function retrieveValidated($class, $limit = null, $sort = null, $filters = null)
 {
     // Validate the data object class.
     $class = strtolower($class);
     if (in_array($class, array_map('strtolower', ClassInfo::subclassesFor('DataObject'))) && ($configuration = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', array('LOWER(IsFor) = ?' => $class))) && ($temporaryClass = DataObject::get_one($class))) {
         $class = ClassInfo::baseDataClass($temporaryClass->ClassName);
         $visibility = $configuration->APIwesomeVisibility ? explode(',', $configuration->APIwesomeVisibility) : null;
         // Validate the sort and filters.
         $where = array();
         $sortValid = is_array($sort) && count($sort) === 2 && ($order = strtoupper($sort[1])) && ($order === 'ASC' || $order === 'DESC');
         $filterValid = is_array($filters) && count($filters);
         $sorting = array();
         $filtering = array();
         // Grab the appropriate attributes for this data object.
         $columns = array();
         $from = array();
         foreach (ClassInfo::subclassesFor($class) as $subclass) {
             // Determine the tables to join.
             $subclassFields = DataObject::database_fields($subclass);
             if (ClassInfo::hasTable($subclass)) {
                 // Determine the versioned table.
                 $same = $subclass === $class;
                 if ($subclass::has_extension('Versioned')) {
                     $subclass = "{$subclass}_Live";
                 }
                 if (!$same) {
                     $from[] = $subclass;
                 }
             }
             // Prepend the table names.
             $subclassColumns = array();
             foreach ($subclassFields as $column => $type) {
                 $subclassColumn = "{$subclass}.{$column}";
                 $subclassColumns[$subclassColumn] = $type;
                 // Determine the tables to sort and filter on.
                 if ($sortValid && $sort[0] === $column) {
                     $sorting[] = "{$subclassColumn} {$order}";
                 }
                 if ($filterValid && isset($filters[$column])) {
                     $filtering[$subclassColumn] = $filters[$column];
                 }
             }
             $columns = array_merge($columns, $subclassColumns);
         }
         array_shift($columns);
         // Determine the versioned table.
         if ($class::has_extension('Versioned')) {
             $class = "{$class}_Live";
         }
         // Determine ID based sorting and filtering, as these aren't considered database fields.
         if ($sortValid && $sort[0] === 'ID') {
             $sorting[] = "{$class}.ID {$order}";
         }
         if ($filterValid && isset($filters['ID'])) {
             $where["{$class}.ID = ?"] = $filters['ID'];
         }
         // Make sure this data object type has visibility customisation.
         if ($visibility && count($visibility) === count($columns) && in_array('1', $visibility)) {
             // Apply any visibility customisation.
             $select = ' ';
             $iteration = 0;
             foreach ($columns as $attribute => $type) {
                 if (isset($visibility[$iteration]) && $visibility[$iteration]) {
                     $select .= $attribute . ', ';
                     if (isset($filtering[$attribute])) {
                         // Apply the filter if the matching attribute is visible.
                         $column = is_numeric($filtering[$attribute]) ? $attribute : "LOWER({$attribute})";
                         $where["{$column} = ?"] = strtolower($filtering[$attribute]);
                     }
                 }
                 $iteration++;
             }
             if (isset($filtering["{$class}.ClassName"])) {
                 $where["LOWER({$class}.ClassName) = ?"] = strtolower($filtering["{$class}.ClassName"]);
             }
             // Grab all data object visible attributes.
             $query = new SQLSelect("{$class}.ClassName,{$select}{$class}.ID", $class, $where, $sorting, array(), array(), is_numeric($limit) ? $limit : array());
             // Determine the tables with visible attributes to join.
             foreach ($from as $join) {
                 if (strpos($select, " {$join}.") !== false) {
                     $query->addLeftJoin($join, "{$class}.ID = {$join}.ID");
                 }
             }
             $objects = array();
             foreach ($query->execute() as $temporary) {
                 // Return an array of data object maps.
                 $object = array();
                 foreach ($temporary as $attribute => $value) {
                     if ($value) {
                         $object[$attribute] = $value;
                     }
//.........这里部分代码省略.........