PHP SQLQuery::addLeftJoin方法代码示例

 /**
  * Manipulates the SQL query
  *
  * @param SQLQuery &$query Query to manipulate
  * 
  * @return void
  *
  * @author Sebastian Diel <sdiel@pixeltricks.de>
  * @since 04.05.2012
  */
 public function augmentSQL(SQLQuery &$query)
 {
     if (!$query->isJoinedTo($this->getLanguageClassName()) && !$query->getDelete()) {
         $silvercartDefaultLocale = SilvercartConfig::Locale();
         $query->addLeftJoin($this->getLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"%s\")", $this->getBaseClassName(), $this->getLanguageClassName(), $this->getRelationFieldName()));
         $addToWhere = '';
         if ($this->getBaseLanguageClassName() != $this->getLanguageClassName()) {
             $query->addLeftJoin($this->getBaseLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"ID\")", $this->getLanguageClassName(), $this->getBaseLanguageClassName()));
             $addToWhere = sprintf("AND \"%s\".\"ID\" = \"%s\".\"ID\"", $this->getBaseLanguageClassName(), $this->getLanguageClassName());
         }
         if (SilvercartConfig::useDefaultLanguageAsFallback() && Translatable::get_current_locale() != $silvercartDefaultLocale && !empty($silvercartDefaultLocale)) {
             $query->addWhere(sprintf("\"%s\".\"Locale\" = IFNULL((%s), (%s)) %s", $this->getBaseLanguageClassName(), $this->getLocaleDependantSelect(Translatable::get_current_locale()), $this->getLocaleDependantSelect($silvercartDefaultLocale), $addToWhere));
         } elseif (!empty($silvercartDefaultLocale)) {
             $query->addWhere(sprintf("\"%s\".\"Locale\" = '%s' %s", $this->getBaseLanguageClassName(), Translatable::get_current_locale(), $addToWhere));
         }
     }
 }

 private function getCSVColumns($flexi)
 {
     $columns = array('SubmittedBy' => 'Submitted By', 'IPAddress' => 'IP Address', 'Created' => 'Created');
     $sql = new SQLQuery();
     $sql->setFrom('FlexiFormSubmissionValue');
     $sql->setSelect('"FlexiFormSubmissionValue"."Name"');
     $sql->addLeftJoin('FlexiFormSubmission', '"FlexiFormSubmissionValue"."SubmissionID" = "FlexiFormSubmission"."ID"');
     $sql->addWhere('"FlexiFormSubmission"."FlexiFormID" = ' . $flexi->ID);
     $sql->addWhere('"FlexiFormSubmission"."FlexiFormClass" = \'' . $flexi->class . '\'');
     $sql->setDistinct(true);
     foreach ($sql->execute() as $row) {
         $columns['Values.' . $row['Name']] = $row['Name'];
     }
     return $columns;
 }

 function exportCompanyData()
 {
     $params = $this->owner->getRequest()->getVars();
     if (!isset($params['report_name']) || empty($params['report_name']) || !count($params['report_name'])) {
         return $this->owner->httpError('412', 'missing required param report_name');
     }
     if (!isset($params['extension']) || empty($params['extension'])) {
         return $this->owner->httpError('412', 'missing required param extension');
     }
     $report_name = isset($params['report_name']) ? $params['report_name'] : '';
     $fields = isset($params['fields']) ? $params['fields'] : array();
     $ext = $params['extension'];
     $query = new SQLQuery();
     if ($report_name) {
         switch ($report_name) {
             case 'sponsorship_type':
                 $query->setFrom('Company');
                 $query->addLeftJoin('SummitSponsorPage_Companies', 'SummitSponsorPage_Companies.CompanyID = Company.ID');
                 $query->addLeftJoin('Summit', 'Summit.ID = SummitSponsorPage_Companies.SummitID');
                 $query->addWhere('Summit.Active', '1');
                 $fields = array_merge($fields, array('Sponsorship' => 'SummitSponsorPage_Companies.SponsorshipType', 'Summit ID' => 'Summit.ID'));
                 $query->setSelect($fields);
                 $query->addOrderBy('SummitSponsorPage_Companies.SponsorshipType');
                 $filename = "Sponsorship_Levels_" . date('Ymd') . "." . $ext;
                 break;
             case 'member_level':
                 $query->setFrom('Company');
                 array_push($fields, 'Company.MemberLevel');
                 $query->setSelect($fields);
                 $filename = "Foundation_Levels_" . date('Ymd') . "." . $ext;
                 break;
             case 'users_roles':
                 $query->setFrom('Company');
                 $query->addInnerJoin('Company_Administrators', 'Company_Administrators.CompanyID = Company.ID');
                 $query->addLeftJoin('Member', 'Member.ID = Company_Administrators.MemberID');
                 $query->addLeftJoin('Group', 'Group.ID = Company_Administrators.GroupID');
                 array_push($fields, 'Group.Title');
                 $query->setSelect($fields);
                 $query->addOrderBy('Company.Name');
                 $filename = "User_Roles_" . date('Ymd') . "." . $ext;
                 break;
             case 'affiliates':
                 $query->setFrom('Org');
                 $query->addLeftJoin('Affiliation', 'Affiliation.OrganizationID = Org.ID');
                 $query->addLeftJoin('Member', 'Member.ID = Affiliation.MemberID');
                 $fields = array_merge($fields, array('Is Current' => 'Affiliation.Current', 'Job Title' => 'Affiliation.JobTitle'));
                 $query->setSelect($fields);
                 $query->addOrderBy('Org.Name');
                 $filename = "Employees_Affiliates_" . date('Ymd') . "." . $ext;
                 break;
             case 'deployments':
                 $query->setFrom('Org');
                 $query->addInnerJoin('Deployment', 'Deployment.OrgID = Org.ID');
                 $custom_fields = array('Creation' => 'Deployment.Created', 'Edited' => 'Deployment.LastEdited', 'Label' => 'Deployment.Label', 'Is Public' => 'Deployment.IsPublic');
                 $fields = array_merge($fields, $custom_fields);
                 $query->setSelect($fields);
                 $query->selectField("CONCAT('http://openstack.org/sangria/DeploymentDetails/',Deployment.ID)", "Link");
                 $query->addOrderBy('Org.Name');
                 $filename = "Deployments_" . date('Ymd') . "." . $ext;
                 break;
             case 'deployment_surveys':
                 $query->setFrom('Org');
                 $query->addLeftJoin('DeploymentSurvey', 'DeploymentSurvey.OrgID = Org.ID');
                 $query->addLeftJoin('Member', 'DeploymentSurvey.MemberID = Member.ID');
                 $custom_fields = array('Creation' => 'DeploymentSurvey.Created', 'Edited' => 'DeploymentSurvey.LastEdited', 'Title' => 'DeploymentSurvey.Title', 'City' => 'DeploymentSurvey.PrimaryCity', 'State' => 'DeploymentSurvey.PrimaryState', 'Country' => 'DeploymentSurvey.PrimaryCountry', 'Org Size' => 'DeploymentSurvey.OrgSize', 'Is Group Member' => 'DeploymentSurvey.UserGroupMember', 'Group Name' => 'DeploymentSurvey.UserGroupName', 'Ok to Contact' => 'DeploymentSurvey.OkToContact');
                 //insert custom fields after org fields
                 $pos = -1;
                 foreach ($fields as $field) {
                     $pos++;
                     if (strpos($field, 'Org') !== false) {
                         continue;
                     } else {
                         array_splice($fields, $pos, 0, $custom_fields);
                         break;
                     }
                 }
                 $query->setSelect($fields);
                 $query->selectField("CONCAT('http://openstack.org/sangria/SurveyDetails/',DeploymentSurvey.ID)", "Link");
                 $filename = "Deployment_Surveys" . date('Ymd') . "." . $ext;
                 break;
             case 'speakers':
                 $query->setFrom('PresentationSpeaker');
                 $query->addLeftJoin('Affiliation', 'Affiliation.MemberID = PresentationSpeaker.MemberID');
                 $query->addLeftJoin('Org', 'Affiliation.OrganizationID = Org.ID');
                 $query->addLeftJoin('Summit', 'Summit.ID = PresentationSpeaker.SummitID');
                 $custom_fields = array('Speaker Name' => 'PresentationSpeaker.FirstName', 'Speaker Surname' => 'PresentationSpeaker.LastName', 'Summit' => 'Summit.Name');
                 $fields = array_merge($fields, $custom_fields);
                 $query->setSelect($fields);
                 $filename = "Speakers_" . date('Ymd') . "." . $ext;
                 break;
         }
     }
     //die($query->sql());
     $result = $query->execute();
     $delimiter = $ext == 'xls' ? "\t" : ",";
     return CSVExporter::getInstance()->export($filename, $result, $delimiter);
 }

 /**
  * 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 = $rel;
                 $realModelClass = ClassInfo::table_for_object_field($modelClass, "{$foreignKey}ID");
                 $this->query->addLeftJoin($component, "\"{$component}\".\"ID\" = \"{$realModelClass}\".\"{$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;
 }

 /**
  * 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->setSelect('SUM("Quantity")');
     $taken->setFrom('EventRegistration_Tickets');
     $taken->addLeftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
     if ($excludeId) {
         $taken->addWhere('"EventRegistration"."ID" <>' . (int) $excludeId);
     }
     $taken->addWhere('"Status" <> \'Canceled\'');
     $taken->addWhere('"EventRegistration"."TimeID" =' . (int) $this->ID);
     $taken = $taken->execute()->value();
     return $this->Capacity >= $taken ? $this->Capacity - $taken : false;
 }

 /**
  *	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', "LOWER(IsFor) = '" . Convert::raw2sql($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.
         if (is_subclass_of($class, 'SiteTree')) {
             $where[] = "ClassName = '{$class}'";
             $class = 'SiteTree';
         } else {
             if (is_subclass_of($class, 'File')) {
                 $where[] = "ClassName = '{$class}'";
                 $class = 'File';
             }
         }
         $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] = is_numeric($filters[$column]) ? "{$subclassColumn} = " . (int) $filters[$column] : "LOWER({$subclassColumn}) = '" . Convert::raw2sql(strtolower($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 = " . (int) $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.
                         $where[] = $filtering[$attribute];
                     }
                 }
                 $iteration++;
             }
             if (isset($filtering["{$class}.ClassName"])) {
                 $where[] = $filtering["{$class}.ClassName"];
             }
             // Grab all data object visible attributes.
             $query = new SQLQuery("{$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");
                 }
             }
//.........这里部分代码省略.........

 /**
  * Returns a number of topseller products.
  * 
  * @return ArrayList
  * 
  * @author Sebastian Diel <sdiel@pixeltricks.de>,
  *         Sascha Koehler <skoehler@pixeltricks.de>
  * @since 03.02.2015
  */
 public function Elements()
 {
     if (!$this->numberOfProductsToShow) {
         $this->numberOfProductsToShow = SilvercartTopsellerProductsWidget::$defaults['numberOfProductsToShow'];
     }
     $cachekey = 'TopsellerProducts' . $this->numberOfProductsToShow;
     $cache = SS_Cache::factory($cachekey);
     $cachedResult = $cache->load($cachekey);
     if ($cachedResult) {
         $result = unserialize($result);
     } else {
         $products = array();
         $sqlQuery = new SQLQuery();
         $sqlQuery->selectField('SOP.SilvercartProductID');
         $sqlQuery->selectField('SUM(SOP.Quantity) AS OrderedQuantity');
         $sqlQuery->addFrom('SilvercartOrderPosition SOP');
         $sqlQuery->addLeftJoin('SilvercartProduct', 'SP.ID = SOP.SilvercartProductID', 'SP');
         $sqlQuery->addWhere('SP.isActive = 1');
         $sqlQuery->addGroupBy('SOP.SilvercartProductID');
         $sqlQuery->addOrderBy('OrderedQuantity', 'DESC');
         $sqlQuery->setLimit($this->numberOfProductsToShow);
         $sqlResult = $sqlQuery->execute();
         foreach ($sqlResult as $row) {
             $product = DataObject::get_by_id('SilvercartProduct', $row['SilvercartProductID']);
             $product->addCartFormIdentifier = $this->ID . '_' . $product->ID;
             $products[] = $product;
         }
         $result = new ArrayList($products);
     }
     return $result;
 }

 public function testJoinSubSelect()
 {
     // Test sub-select works
     $query = new SQLQuery();
     $query->setFrom('"MyTable"');
     $query->addInnerJoin('(SELECT * FROM "MyOtherTable")', '"Mot"."MyTableID" = "MyTable"."ID"', 'Mot');
     $query->addLeftJoin('(SELECT "MyLastTable"."MyOtherTableID", COUNT(1) as "MyLastTableCount" ' . 'FROM "MyLastTable" GROUP BY "MyOtherTableID")', '"Mlt"."MyOtherTableID" = "Mot"."ID"', 'Mlt');
     $query->setOrderBy('COALESCE("Mlt"."MyLastTableCount", 0) DESC');
     $this->assertEquals('SELECT *, COALESCE("Mlt"."MyLastTableCount", 0) AS "_SortColumn0" FROM "MyTable" ' . 'INNER JOIN (SELECT * FROM "MyOtherTable") AS "Mot" ON "Mot"."MyTableID" = "MyTable"."ID" ' . 'LEFT JOIN (SELECT "MyLastTable"."MyOtherTableID", COUNT(1) as "MyLastTableCount" FROM "MyLastTable" ' . 'GROUP BY "MyOtherTableID") AS "Mlt" ON "Mlt"."MyOtherTableID" = "Mot"."ID" ' . 'ORDER BY "_SortColumn0" DESC', $query->sql());
     // Test that table names do not get mistakenly identified as sub-selects
     $query = new SQLQuery();
     $query->setFrom('"MyTable"');
     $query->addInnerJoin('NewsArticleSelected', '"News"."MyTableID" = "MyTable"."ID"', 'News');
     $this->assertEquals('SELECT * FROM "MyTable" INNER JOIN "NewsArticleSelected" AS "News" ON ' . '"News"."MyTableID" = "MyTable"."ID"', $query->sql());
 }

 public function testJoinSubSelect()
 {
     $query = new SQLQuery();
     $query->setFrom('MyTable');
     $query->addInnerJoin('(SELECT * FROM MyOtherTable)', 'Mot.MyTableID = MyTable.ID', 'Mot');
     $query->addLeftJoin('(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID)', 'Mlt.MyOtherTableID = Mot.ID', 'Mlt');
     $query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC');
     $this->assertSQLEquals('SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' . 'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' . 'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' . 'ORDER BY "_SortColumn0" DESC', $query->sql($parameters));
 }

 /**
  * Returns the number of tickets available for an event time.
  *
  * @param  RegistrableDateTime $time
  * @param  int $excludeId A registration ID to exclude from calculations.
  * @return array
  */
 public function getAvailableForDateTime(RegistrableDateTime $time, $excludeId = null)
 {
     if ($this->StartType == 'Date') {
         $start = strtotime($this->StartDate);
     } else {
         $start = $time->getStartDateTime()->getTimestamp();
         $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->getStartDateTime()->getTimestamp();
         $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->setSelect('SUM("Quantity")');
     $booked->setFrom('"EventRegistration_Tickets"');
     $booked->addLeftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
     if ($excludeId) {
         $booked->addWhere('"EventRegistration"."ID"', '<>', $excludeId);
     }
     $booked->addWhere('"Status"', '<>', 'Canceled');
     $booked->addWhere('"EventTicketID"', $this->ID);
     $booked->addWhere('"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.');
     }
 }

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

 /**
  * Update any requests to limit the results to the current site
  */
 public 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->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)");
             }
         }
         // WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
         $select = $query->getSelect();
         if ($hasGroupSubsites && $subsiteID && isset($select[0]) && !$select[0] == 'COUNT(*)') {
             $query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
         }
     }
 }

 /**
  * Retrieves Locations by lat, long, distance, and optionally a limit.
  */
 public function getLocationSQLResultsByLatLong($lat = 37, $long = -122, $distance = 25, $limit = null)
 {
     //$data = DB::query('SELECT "ID" FROM "Marker" LIMIT 0 , '.$limit.';')->value();
     //$query = 'SELECT "ID", ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('.$long.') ) + sin( radians('.$lat.') ) * sin( radians( Latitude ) ) ) ) AS "Distance" FROM "Marker" HAVING "Distance" < '.$distance.' ORDER BY "Distance" LIMIT 0 , '.$limit.';';
     $markerClass = StoreFinder::$MarkerClass;
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom($markerClass);
     $sqlQuery->selectField('*');
     $sqlQuery->selectField('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat . ') ) * sin( radians( Latitude ) ) ) )', 'Distance');
     $sqlQuery->setHaving("Distance < " . $distance);
     $sqlQuery->setOrderBy('Distance');
     $sqlQuery->setLimit($limit);
     if ($markerClass != 'Marker') {
         $sqlQuery->addLeftJoin("Marker", 'Marker.ID = ' . $markerClass . '.ID');
     }
     $this->extraSQL($sqlQuery);
     // Execute and return a Query object
     $result = $sqlQuery->execute();
     return $result;
 }

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

 /**
  * @return SQLQuery
  */
 protected function getRemoteObjectsQuery()
 {
     // Get all tables to query
     $tables = $this->getRemoteClassHierarchy();
     $baseClass = array_shift($tables);
     // Generate sql query
     $query = new SQLQuery("\"{$baseClass}\".*", "\"{$baseClass}\"", $this->targetWhere);
     $query->setOrderBy("\"{$baseClass}\".\"ID\" ASC");
     // Sort by ID for some performance reasons
     foreach ($tables as $class) {
         $query->addSelect("\"{$class}\".*");
         $query->addLeftJoin($class, "\"{$baseClass}\".\"ID\" = \"{$class}\".\"ID\"");
     }
     return $query;
 }