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