PHP SQLQuery::leftJoin方法代码示例

 /**
  * Traverse the relationship fields, and add the table
  * mappings to the query object state.
  * 
  * @todo try to make this implicitly triggered so it doesn't have to be manually called in child filters
  * @param SQLQuery $query
  * @return SQLQuery
  */
 function applyRelation($query)
 {
     if (is_array($this->relation)) {
         foreach ($this->relation as $rel) {
             $model = singleton($this->model);
             if ($component = $model->has_one($rel)) {
                 if (!$query->isJoinedTo($component)) {
                     $foreignKey = $model->getReverseAssociation($component);
                     $query->leftJoin($component, "`{$component}`.`ID` = `{$this->model}`.`{$foreignKey}ID`");
                 }
                 $this->model = $component;
             } elseif ($component = $model->has_many($rel)) {
                 if (!$query->isJoinedTo($component)) {
                     $ancestry = $model->getClassAncestry();
                     $foreignKey = $model->getComponentJoinField($rel);
                     $query->leftJoin($component, "`{$component}`.`{$foreignKey}` = `{$ancestry[0]}`.`ID`");
                 }
                 $this->model = $component;
             } elseif ($component = $model->many_many($rel)) {
                 list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
                 $parentBaseClass = ClassInfo::baseDataClass($parentClass);
                 $componentBaseClass = ClassInfo::baseDataClass($componentClass);
                 $query->innerJoin($relationTable, "`{$relationTable}`.`{$parentField}` = `{$parentBaseClass}`.`ID`");
                 $query->leftJoin($componentClass, "`{$relationTable}`.`{$componentField}` = `{$componentClass}`.`ID`");
                 $this->model = $componentClass;
                 // Experimental support for user-defined relationships via a "(relName)Query" method
                 // This will likely be dropped in 2.4 for a system that makes use of Lazy Data Lists.
             } elseif ($model->hasMethod($rel . 'Query')) {
                 // Get the query representing the join - it should have "$ID" in the filter
                 $newQuery = $model->{"{$rel}Query"}();
                 if ($newQuery) {
                     // Get the table to join to
                     $newModel = str_replace('`', '', array_shift($newQuery->from));
                     // Get the filter to use on the join
                     $ancestry = $model->getClassAncestry();
                     $newFilter = "(" . str_replace('$ID', "`{$ancestry[0]}`.`ID`", implode(") AND (", $newQuery->where)) . ")";
                     $query->leftJoin($newModel, $newFilter);
                     $this->model = $newModel;
                 } else {
                     $this->name = "NULL";
                     return;
                 }
             }
         }
     }
     return $query;
 }

 public function testInnerJoin()
 {
     $query = new SQLQuery();
     $query->from('MyTable');
     $query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
     $query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
     $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
     $query = new SQLQuery();
     $query->from('MyTable');
     $query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
     $query->leftJoin('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());
 }

	/**
	 * 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 $relation The array/dot-syntax relation to follow
	 * @return The model class of the related item
	 */
	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->leftJoin($component, "\"$component\".\"ID\" = \"{$modelClass}\".\"{$foreignKey}ID\"");
				
    				/**
    				 * add join clause to the component's ancestry classes so that the search filter could search on its 
    				 * ancester fields.
    				 */
    				$ancestry = ClassInfo::ancestry($component, true);
    				if(!empty($ancestry)){
    					$ancestry = array_reverse($ancestry);
    					foreach($ancestry as $ancestor){
    						if($ancestor != $component){
    							$this->query->innerJoin($ancestor, "\"$component\".\"ID\" = \"$ancestor\".\"ID\"");
    							$component=$ancestor;
    						}
    					}
    				}
    			}
    			$modelClass = $component;

    		} elseif ($component = $model->has_many($rel)) {
    			if(!$this->query->isJoinedTo($component)) {
    			 	$ancestry = $model->getClassAncestry();
    				$foreignKey = $model->getRemoteJoinField($rel);
    				$this->query->leftJoin($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->innerJoin($ancestor, "\"$component\".\"ID\" = \"$ancestor\".\"ID\"");
    							$component=$ancestor;
    						}
    					}
    				}
    			}
    			$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->innerJoin($relationTable, "\"$relationTable\".\"$parentField\" = \"$parentBaseClass\".\"ID\"");
    			$this->query->leftJoin($componentBaseClass, "\"$relationTable\".\"$componentField\" = \"$componentBaseClass\".\"ID\"");
    			if(ClassInfo::hasTable($componentClass)) {
    				$this->query->leftJoin($componentClass, "\"$relationTable\".\"$componentField\" = \"$componentClass\".\"ID\"");
    			}
    			$modelClass = $componentClass;

    		}
		}
		
		return $modelClass;
	}

 /**
  * Returns the number of tickets available for an event time.
  *
  * @param  RegisterableDateTime $time
  * @param  int $excludeId A registration ID to exclude from calculations.
  * @return array
  */
 public function getAvailableForDateTime(RegisterableDateTime $time, $excludeId = null)
 {
     if ($this->StartType == 'Date') {
         $start = strtotime($this->StartDate);
     } else {
         $start = $time->getStartTimestamp();
         $start = sfTime::subtract($start, $this->StartDays, sfTime::DAY);
         $start = sfTime::subtract($start, $this->StartHours, sfTime::HOUR);
         $start = sfTime::subtract($start, $this->StartMins, sfTime::MINUTE);
     }
     if ($start >= time()) {
         return array('available' => false, 'reason' => 'Tickets are not yet available.', 'available_at' => $start);
     }
     if ($this->EndType == 'Date') {
         $end = strtotime($this->EndDate);
     } else {
         $end = $time->getStartTimestamp();
         $end = sfTime::subtract($end, $this->EndDays, sfTime::DAY);
         $end = sfTime::subtract($end, $this->EndHours, sfTime::HOUR);
         $end = sfTime::subtract($end, $this->EndMins, sfTime::MINUTE);
     }
     if (time() >= $end) {
         return array('available' => false, 'reason' => 'Tickets are no longer available.');
     }
     if (!($quantity = $this->Available)) {
         return array('available' => true);
     }
     $booked = new SQLQuery();
     $booked->select('SUM("Quantity")');
     $booked->from('"EventRegistration_Tickets"');
     $booked->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
     if ($excludeId) {
         $booked->where('"EventRegistration"."ID"', '<>', $excludeId);
     }
     $booked->where('"Status"', '<>', 'Canceled');
     $booked->where('"EventTicketID"', $this->ID);
     $booked->where('"EventRegistration"."TimeID"', $time->ID);
     $booked = $booked->execute()->value();
     if ($booked < $quantity) {
         return array('available' => $quantity - $booked);
     } else {
         return array('available' => false, 'reason' => 'All tickets have been booked.');
     }
 }

 /**
  * Update any requests to limit the results to the current site
  */
 function augmentSQL(SQLQuery &$query)
 {
     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->from as $item) {
             if (strpos($item, 'Group_Subsites') !== false) {
                 $hasGroupSubsites = true;
                 break;
             }
         }
         if (!$hasGroupSubsites) {
             if ($subsiteID) {
                 $query->leftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\" \n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
                 $query->where[] = "(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)";
             } else {
                 $query->where[] = "\"Group\".\"AccessAllSubsites\" = 1";
             }
         }
         // WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
         if (!$query->select[0] == 'COUNT(*)') {
             $query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
         }
     }
 }

 /**
  * 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.
  * 
  * @todo try to make this implicitly triggered so it doesn't have to be manually called in child filters
  * @param SQLQuery $query
  * @return SQLQuery
  */
 function applyRelation($query)
 {
     if (is_array($this->relation)) {
         foreach ($this->relation as $rel) {
             $model = singleton($this->model);
             if ($component = $model->has_one($rel)) {
                 if (!$query->isJoinedTo($component)) {
                     $foreignKey = $model->getReverseAssociation($component);
                     $query->leftJoin($component, "\"{$component}\".\"ID\" = \"{$this->model}\".\"{$foreignKey}ID\"");
                     /**
                      * add join clause to the component's ancestry classes so that the search filter could search on its 
                      * ancester fields.
                      */
                     $ancestry = ClassInfo::ancestry($component, true);
                     if (!empty($ancestry)) {
                         $ancestry = array_reverse($ancestry);
                         foreach ($ancestry as $ancestor) {
                             if ($ancestor != $component) {
                                 $query->innerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
                                 $component = $ancestor;
                             }
                         }
                     }
                 }
                 $this->model = $component;
             } elseif ($component = $model->has_many($rel)) {
                 if (!$query->isJoinedTo($component)) {
                     $ancestry = $model->getClassAncestry();
                     $foreignKey = $model->getRemoteJoinField($rel);
                     $query->leftJoin($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) {
                                 $query->innerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
                                 $component = $ancestor;
                             }
                         }
                     }
                 }
                 $this->model = $component;
             } elseif ($component = $model->many_many($rel)) {
                 list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
                 $parentBaseClass = ClassInfo::baseDataClass($parentClass);
                 $componentBaseClass = ClassInfo::baseDataClass($componentClass);
                 $query->innerJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
                 $query->leftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
                 if (ClassInfo::hasTable($componentClass)) {
                     $query->leftJoin($componentClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentClass}\".\"ID\"");
                 }
                 $this->model = $componentClass;
                 // Experimental support for user-defined relationships via a "(relName)Query" method
                 // This will likely be dropped in 2.4 for a system that makes use of Lazy Data Lists.
             } elseif ($model->hasMethod($rel . 'Query')) {
                 // Get the query representing the join - it should have "$ID" in the filter
                 $newQuery = $model->{"{$rel}Query"}();
                 if ($newQuery) {
                     // Get the table to join to
                     //DATABASE ABSTRACTION: I don't think we need this line anymore:
                     $newModel = str_replace('`', '', array_shift($newQuery->from));
                     // Get the filter to use on the join
                     $ancestry = $model->getClassAncestry();
                     $newFilter = "(" . str_replace('$ID', "\"{$ancestry[0]}\".\"ID\"", implode(") AND (", $newQuery->where)) . ")";
                     $query->leftJoin($newModel, $newFilter);
                     $this->model = $newModel;
                 } else {
                     $this->name = "NULL";
                     return;
                 }
             }
         }
     }
     return $query;
 }

 /**
  * Returns the overall number of places remaining at this event, TRUE if
  * there are unlimited places or FALSE if they are all taken.
  *
  * @param  int $excludeId A registration ID to exclude from calculations.
  * @return int|bool
  */
 public function getRemainingCapacity($excludeId = null)
 {
     if (!$this->Capacity) {
         return true;
     }
     $taken = new SQLQuery();
     $taken->select('SUM("Quantity")');
     $taken->from('EventRegistration_Tickets');
     $taken->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
     if ($excludeId) {
         $taken->where('"EventRegistration"."ID"', '<>', $excludeId);
     }
     $taken->where('"Status"', '<>', 'Canceled');
     $taken->where('"EventRegistration"."TimeID"', $this->ID);
     $taken = $taken->execute()->value();
     return $this->Capacity >= $taken ? $this->Capacity - $taken : false;
 }