public function __construct(DataQuery $base, $connective) { parent::__construct($base->dataClass); $this->query = $base->query; $this->whereQuery = new SQLSelect(); $this->whereQuery->setConnective($connective); $base->where($this); }
protected function excludeMany(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $values = $this->getValue(); $comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, false, true, $this->getCaseSensitive(), true); $parameters = array(); foreach ($values as $value) { $parameters[] = $this->getMatchPattern($value); } // Since query connective is ambiguous, use AND explicitly here $count = count($values); $predicate = implode(' AND ', array_fill(0, $count, $comparisonClause)); return $query->where(array($predicate => $parameters)); }
protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $predicate = sprintf("NOT MATCH (%s) AGAINST (?)", $this->getDbName()); return $query->where(array($predicate => $this->getValue())); }
/** * Loads all the stub fields that an initial lazy load didn't load fully. * * @param string $class Class to load the values from. Others are joined as required. * Not specifying a tableClass will load all lazy fields from all tables. * @return bool Flag if lazy loading succeeded */ protected function loadLazyFields($class = null) { if (!$this->isInDB() || !is_numeric($this->ID)) { return false; } if (!$class) { $loaded = array(); foreach ($this->record as $key => $value) { if (strlen($key) > 5 && substr($key, -5) == '_Lazy' && !array_key_exists($value, $loaded)) { $this->loadLazyFields($value); $loaded[$value] = $value; } } return false; } $dataQuery = new DataQuery($class); // Reset query parameter context to that of this DataObject if ($params = $this->getSourceQueryParams()) { foreach ($params as $key => $value) { $dataQuery->setQueryParam($key, $value); } } // Limit query to the current record, unless it has the Versioned extension, // in which case it requires special handling through augmentLoadLazyFields() $schema = static::getSchema(); $baseIDColumn = $schema->sqlColumnForField($this, 'ID'); $dataQuery->where([$baseIDColumn => $this->record['ID']])->limit(1); $columns = array(); // Add SQL for fields, both simple & multi-value // TODO: This is copy & pasted from buildSQL(), it could be moved into a method $databaseFields = $schema->databaseFields($class, false); foreach ($databaseFields as $k => $v) { if (!isset($this->record[$k]) || $this->record[$k] === null) { $columns[] = $k; } } if ($columns) { $query = $dataQuery->query(); $this->extend('augmentLoadLazyFields', $query, $dataQuery, $this); $this->extend('augmentSQL', $query, $dataQuery); $dataQuery->setQueriedColumns($columns); $newData = $dataQuery->execute()->record(); // Load the data into record if ($newData) { foreach ($newData as $k => $v) { if (in_array($k, $columns)) { $this->record[$k] = $v; $this->original[$k] = $v; unset($this->record[$k . '_Lazy']); } } // No data means that the query returned nothing; assign 'null' to all the requested fields } else { foreach ($columns as $k) { $this->record[$k] = null; $this->original[$k] = null; unset($this->record[$k . '_Lazy']); } } } return true; }
/** * Applies matches for several values, either as inclusive or exclusive * * @param DataQuery $query * @param bool $inclusive True if this is inclusive, or false if exclusive * @return DataQuery */ protected function manyFilter(DataQuery $query, $inclusive) { $this->model = $query->applyRelation($this->relation); $caseSensitive = $this->getCaseSensitive(); // Check values for null $field = $this->getDbName(); $values = $this->getValue(); if (empty($values)) { throw new \InvalidArgumentException("Cannot filter {$field} against an empty set"); } $hasNull = in_array(null, $values, true); if ($hasNull) { $values = array_filter($values, function ($value) { return $value !== null; }); } $connective = ''; if (empty($values)) { $predicate = ''; } elseif ($caseSensitive === null) { // For queries using the default collation (no explicit case) we can use the WHERE .. NOT IN .. syntax, // providing simpler SQL than many WHERE .. AND .. fragments. $column = $this->getDbName(); $placeholders = DB::placeholders($values); if ($inclusive) { $predicate = "{$column} IN ({$placeholders})"; } else { $predicate = "{$column} NOT IN ({$placeholders})"; } } else { // Generate reusable comparison clause $comparisonClause = DB::get_conn()->comparisonClause($this->getDbName(), null, true, !$inclusive, $this->getCaseSensitive(), true); $count = count($values); if ($count > 1) { $connective = $inclusive ? ' OR ' : ' AND '; $conditions = array_fill(0, $count, $comparisonClause); $predicate = implode($connective, $conditions); } else { $predicate = $comparisonClause; } } // Always check for null when doing exclusive checks (either AND IS NOT NULL / OR IS NULL) // or when including the null value explicitly (OR IS NULL) if ($hasNull || !$inclusive) { // If excluding values which don't include null, or including // values which include null, we should do an `OR IS NULL`. // Otherwise we are excluding values that do include null, so `AND IS NOT NULL`. // Simplified from (!$inclusive && !$hasNull) || ($inclusive && $hasNull); $isNull = !$hasNull || $inclusive; $nullCondition = DB::get_conn()->nullCheckClause($field, $isNull); // Determine merge strategy if (empty($predicate)) { $predicate = $nullCondition; } else { // Merge null condition with predicate if ($isNull) { $nullCondition = " OR {$nullCondition}"; } else { $nullCondition = " AND {$nullCondition}"; } // If current predicate connective doesn't match the same as the null connective // make sure to group the prior condition if ($connective && ($connective === ' OR ') !== $isNull) { $predicate = "({$predicate})"; } $predicate .= $nullCondition; } } return $query->where(array($predicate => $values)); }
protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $predicate = sprintf('%1$s < ? OR %1$s > ?', $this->getDbName()); return $query->where(array($predicate => array($this->min, $this->max))); }
/** * Applies a exclusion(inverse) filter to the query * Handles SQL escaping for both numeric and string values * * @param DataQuery $query * @return $this|DataQuery */ protected function excludeOne(DataQuery $query) { $this->model = $query->applyRelation($this->relation); $predicate = sprintf("%s %s ?", $this->getDbName(), $this->getInverseOperator()); return $query->where(array($predicate => $this->getDbFormattedValue())); }
/** * For lazy loaded fields requiring extra sql manipulation, ie versioning. * * @param SQLSelect $query * @param DataQuery $dataQuery * @param DataObject $dataObject */ public function augmentLoadLazyFields(SQLSelect &$query, DataQuery &$dataQuery = null, $dataObject) { // The VersionedMode local variable ensures that this decorator only applies to // queries that have originated from the Versioned object, and have the Versioned // metadata set on the query object. This prevents regular queries from // accidentally querying the *_versions tables. $versionedMode = $dataObject->getSourceQueryParam('Versioned.mode'); $modesToAllowVersioning = array('all_versions', 'latest_versions', 'archive', 'version'); if (!empty($dataObject->Version) && (!empty($versionedMode) && in_array($versionedMode, $modesToAllowVersioning))) { // This will ensure that augmentSQL will select only the same version as the owner, // regardless of how this object was initially selected $versionColumn = $this->owner->getSchema()->sqlColumnForField($this->owner, 'Version'); $dataQuery->where([$versionColumn => $dataObject->Version]); $dataQuery->setQueryParam('Versioned.mode', 'all_versions'); } }
/** * Tests that getFinalisedQuery can include all tables */ public function testConditionsIncludeTables() { // Including filter on parent table only doesn't pull in second $query = new DataQuery('DataQueryTest_C'); $query->sort('"SortOrder"'); $query->where(array('"DataQueryTest_C"."Title" = ?' => array('First'))); $result = $query->getFinalisedQuery(array('Title')); $from = $result->getFrom(); $this->assertContains('DataQueryTest_C', array_keys($from)); $this->assertNotContains('DataQueryTest_E', array_keys($from)); // Including filter on sub-table requires it $query = new DataQuery('DataQueryTest_C'); $query->sort('"SortOrder"'); $query->where(array('"DataQueryTest_C"."Title" = ? OR "DataQueryTest_E"."SortOrder" > ?' => array('First', 2))); $result = $query->getFinalisedQuery(array('Title')); $from = $result->getFrom(); // Check that including "SortOrder" prompted inclusion of DataQueryTest_E table $this->assertContains('DataQueryTest_C', array_keys($from)); $this->assertContains('DataQueryTest_E', array_keys($from)); $arrayResult = iterator_to_array($result->execute()); $first = array_shift($arrayResult); $this->assertNotNull($first); $this->assertEquals('First', $first['Title']); $second = array_shift($arrayResult); $this->assertNotNull($second); $this->assertEquals('Last', $second['Title']); $this->assertEmpty(array_shift($arrayResult)); }