PHP SQLSelect::setWhere方法代码示例

 /**
  * Remove a filter from the query
  *
  * @param string|array $fieldExpression The predicate of the condition to remove
  * (ignoring parameters). The expression will be considered a match if it's
  * contained within any other predicate.
  * @return DataQuery Self reference
  */
 public function removeFilterOn($fieldExpression)
 {
     $matched = false;
     // If given a parameterised condition extract only the condition
     if (is_array($fieldExpression)) {
         reset($fieldExpression);
         $fieldExpression = key($fieldExpression);
     }
     $where = $this->query->getWhere();
     // Iterate through each condition
     foreach ($where as $i => $condition) {
         // Rewrite condition groups as plain conditions before comparison
         if ($condition instanceof SQLConditionGroup) {
             $predicate = $condition->conditionSQL($parameters);
             $condition = array($predicate => $parameters);
         }
         // As each condition is a single length array, do a single
         // iteration to extract the predicate and parameters
         foreach ($condition as $predicate => $parameters) {
             // @see SQLSelect::addWhere for why this is required here
             if (strpos($predicate, $fieldExpression) !== false) {
                 unset($where[$i]);
                 $matched = true;
             }
             // Enforce single-item condition predicate => parameters structure
             break;
         }
     }
     // set the entire where clause back, but clear the original one first
     if ($matched) {
         $this->query->setWhere($where);
     } else {
         throw new InvalidArgumentException("Couldn't find {$fieldExpression} in the query filter.");
     }
     return $this;
 }

 public function testSelectLast()
 {
     // Test last in sequence
     $query = new SQLSelect();
     $query->setFrom('"SQLSelectTest_DO"');
     $query->setOrderBy('"Name"');
     $result = $query->lastRow()->execute();
     $records = array();
     foreach ($result as $row) {
         $records[] = $row;
     }
     $this->assertCount(1, $records);
     $this->assertEquals('Object 2', $records[0]['Name']);
     // Test last from empty sequence
     $query = new SQLSelect();
     $query->setFrom('"SQLSelectTest_DO"');
     $query->setOrderBy('"Name"');
     $query->setWhere(array("\"Name\" = 'Nonexistent Object'"));
     $result = $query->lastRow()->execute();
     $records = array();
     foreach ($result as $row) {
         $records[] = $row;
     }
     $this->assertCount(0, $records);
     // Test that given the first item, the 'last' in this list matches the first
     $query = new SQLSelect();
     $query->setFrom('"SQLSelectTest_DO"');
     $query->setOrderBy('"Name"');
     $query->setLimit(1);
     $result = $query->lastRow()->execute();
     $records = array();
     foreach ($result as $row) {
         $records[] = $row;
     }
     $this->assertCount(1, $records);
     $this->assertEquals('Object 1', $records[0]['Name']);
 }

 /**
  * Copies all values from one table to another. Will override any existing values with matching ID's.
  *
  * @param   string      $fromTable      Name of SOURCE table to copy values from.
  * @param   string      $toTable        Name of DESTINATION table to copy values to.
  * @param   array|null  $fieldMapping   Array of fields to copy (and ONLY these fields). Can also specify key => value
  *                                      pairs to map between old/new names (instead of just values). Note: Leave
  *                                      empty (or pass null) to automatically assume ALL fields from source table (including ID).
  * @param   bool        $purgeDest      Ensures all data in the DESTINATION table matches the source.
  * @param   mixed|null  $where          An optional filter passed directly to ->setWhere() method on SQLSelect.
  * @throws  MigrationException
  */
 public static function copyTable($fromTable, $toTable, array $fieldMapping = null, $purgeDest = false, $where = null)
 {
     if (!static::tableExists($fromTable)) {
         throw new MigrationException("Table '{$fromTable}' does not exist.");
     }
     if (!static::tableExists($toTable)) {
         throw new MigrationException("Table '{$fromTable}' does not exist.");
     }
     // Initialize defaults.
     if ($fieldMapping === null) {
         $fieldMapping = array();
     }
     // Normalize to empty.
     if ($fieldMapping === array()) {
         // If empty: Use all fields from the source.
         $fieldMapping = array_keys(static::getTableColumns($fromTable));
     }
     // Since an ID is required to prevent duplication of data, add it now if it's not already setup.
     // TODO: Should this be optional?
     if (!in_array('ID', $fieldMapping)) {
         $fieldMapping[] = 'ID';
     }
     // Separate out the source/destination fields from the field mapping to help with selection and validation (correspondingly).
     $sourceFields = array_map(function ($key, $value) {
         if (!is_numeric($key)) {
             return $key;
         }
         return $value;
     }, array_keys($fieldMapping), array_values($fieldMapping));
     $destFields = array_values($fieldMapping);
     // Validate columns in the destination first and ensure they exist first before moving forward, since you
     // don't want to perform a DELETE on an entire table unless you're sure the entire operation will complete.
     $destActualFields = array_keys(self::getTableColumns($toTable));
     $destFieldDiff = array_diff($destFields, $destActualFields);
     if (count($destFieldDiff) !== 0) {
         throw new MigrationException("The field(s) '" . join(', ', $destFieldDiff) . "' do not exist in the destination table '{$toTable}'.");
     }
     // Purge now, if specified.
     if ($purgeDest) {
         $delete = new SQLDelete($toTable);
         $delete->execute();
     }
     // Begin fetching rows and copying them over now.
     $select = new SQLSelect($sourceFields, $fromTable);
     if ($where !== null) {
         $select->setWhere($where);
     }
     $result = $select->execute();
     while ($sourceRow = $result->next()) {
         // Convert row fields based on our mapping.
         $destRow = array();
         foreach ($sourceRow as $field => $value) {
             if (array_key_exists($field, $fieldMapping)) {
                 $field = $fieldMapping[$field];
             }
             $destRow[$field] = $value;
         }
         // Update table.
         static::setRowValuesOnTable($toTable, $destRow, null, true);
     }
 }

 /**
  * Find the extra field data for a single row of the relationship join
  * table, given the known child ID.
  *
  * @param string $componentName The name of the component
  * @param int $itemID The ID of the child for the relationship
  *
  * @return array Map of fieldName => fieldValue
  */
 public function getExtraData($componentName, $itemID)
 {
     $result = array();
     // Skip if no extrafields or unsaved record
     if (empty($this->extraFields) || empty($itemID)) {
         return $result;
     }
     if (!is_numeric($itemID)) {
         user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
     }
     $cleanExtraFields = array();
     foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
         $cleanExtraFields[] = "\"{$fieldName}\"";
     }
     $query = new SQLSelect($cleanExtraFields, "\"{$this->joinTable}\"");
     $filter = $this->foreignIDWriteFilter($this->getForeignID());
     if ($filter) {
         $query->setWhere($filter);
     } else {
         user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
     }
     $query->addWhere(array("\"{$this->localKey}\"" => $itemID));
     $queryResult = $query->execute()->current();
     if ($queryResult) {
         foreach ($queryResult as $fieldName => $value) {
             $result[$fieldName] = $value;
         }
     }
     return $result;
 }

 /**
  * Find the extra field data for a single row of the relationship join
  * table, given the known child ID.
  *
  * @param string $componentName The name of the component
  * @param int $itemID The ID of the child for the relationship
  *
  * @return array Map of fieldName => fieldValue
  */
 public function getExtraData($componentName, $itemID)
 {
     $result = array();
     if (!is_numeric($itemID)) {
         user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
     }
     // @todo Optimize into a single query instead of one per extra field
     if ($this->extraFields) {
         foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
             $query = new SQLSelect("\"{$fieldName}\"", "\"{$this->joinTable}\"");
             if ($filter = $this->foreignIDWriteFilter($this->getForeignID())) {
                 $query->setWhere($filter);
             } else {
                 user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
             }
             $query->addWhere("\"{$this->localKey}\" = {$itemID}");
             $result[$fieldName] = $query->execute()->value();
         }
     }
     return $result;
 }