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