public function testRemoveLogicFromStatement() { $clause = new Clause(); $clause->setStatement('AND `loo` IS NOT NULL'); $statement = $clause->removeLogicFromStatement(); $this->assertEquals('`loo` IS NOT NULL', $clause->getStatement()); $this->assertEquals('`loo` IS NOT NULL', $clause->statement()); $clause = new Clause(); $clause->setStatement('OR `loo` IS NOT NULL'); $statement = $clause->removeLogicFromStatement(); $this->assertEquals('`loo` IS NOT NULL', $clause->getStatement()); $this->assertEquals('`loo` IS NOT NULL', $clause->statement()); }
public function filtersToSqlProvider() { $filterA = new Data_Filter(); $criterions = array(FilterType::criteriaToCriterion('and-isActive-bool-field-true'), FilterType::criteriaToCriterion('or-size-gt-field-volume'), FilterType::criteriaToCriterion('and-bar-re-value-^moo$')); $filterA->setCriterions($criterions); $filterB = new Data_Filter(); $criterions = array(FilterType::criteriaToCriterion('and-age-in-value-18,19,20,88'), FilterType::criteriaToCriterion('and-city-nbegins-value-London')); $filterB->setCriterions($criterions); $filterC = new Data_Filter(); $criterions = array(FilterType::criteriaToCriterion('and-foo-nei-field-bar'), FilterType::criteriaToCriterion('or-loo-ne-value-7')); $filterC->setCriterions($criterions)->setLogic('or'); $sql = '((`isActive` IS NOT NULL AND `isActive` != "") OR `size` > `volume` AND `bar` REGEXP :0_filter_2) AND (`age` IN (:1_filter_0_0 COLLATE utf8_bin, :1_filter_0_1 COLLATE utf8_bin, :1_filter_0_2 COLLATE utf8_bin, :1_filter_0_3 COLLATE utf8_bin) AND `city` NOT LIKE CONCAT(:1_filter_1, "%") COLLATE utf8_bin) OR (`foo` != CAST(`bar` AS CHAR) COLLATE utf8_general_ci OR `loo` != CAST(:2_filter_1 AS CHAR) COLLATE utf8_bin)'; $params = array('0_filter_2' => '^moo$', '1_filter_0_0' => '18', '1_filter_0_1' => '19', '1_filter_0_2' => '20', '1_filter_0_3' => '88', '1_filter_1' => 'London', '2_filter_1' => '7'); $clause = new Clause(); $clause->setStatement($sql)->setParameters($params); $data[] = array(array($filterA, $filterB, $filterC), $clause); return $data; }
/** * Turns array of Ucc\Data\Filter\Filter objects (also known as criteria) into SQL * * @param array $filers Array of Ucc\Data\Filter\Filter objects * @param array $fieldMap Array of field names and tables * @param string $namespace Prefix for query placeholders * @return Ucc\Data\Filter\Clause\Clause */ public static function filtersToSqlClause(array $filters, $fieldMap = array()) { // Default return values $sqlClause = new Clause(); $sql = ''; $params = array(); foreach ($filters as $i => $filter) { $clause = self::filterToSqlClause($filter, $fieldMap = array(), $i . '_filter'); if (!empty($sql)) { $sql .= ' ' . strtoupper($filter->getLogic()) . ' '; } $sql .= '(' . $clause->getStatement() . ')'; // Add params $params[] = $clause->getParameters(); $sqlClause->setParameters($clause->getParameters()); } $sqlClause->setStatement($sql); return $sqlClause; }
/** * Turns Criterion into IN Sql Clause * * @param Criterion $criterion Criterion to process * @param string $placeHolder Placeholder for parameter name * @param array $fieldMap Array representing field map */ public static function criterionToInClause(Criterion $criterion, $placeHolder = 'filter_0', $fieldMap = array()) { // Create local operand and collate $op = false; $collate = false; $clause = new Clause(); switch ($criterion->op()) { case 'in': $op = 'IN'; $collate = 'utf8_bin'; break; case 'nin': $op = 'NOT IN'; $collate = 'utf8_bin'; break; case 'ini': $op = 'IN'; $collate = 'utf8_general_ci'; break; case 'nini': $op = 'NOT IN'; $collate = 'utf8_general_ci'; break; } if ($op && $collate) { if ($criterion->type() == Criterion::CRITERION_TYPE_VALUE) { // Values should be in a comma separated list. $values = explode(',', $criterion->value()); // We must add a parameter and a placeholder for each value. $placeHolders = array(); foreach ($values as $key => $value) { $clause->setParameter($placeHolder . '_' . $key, $value); $placeHolders[] = ':' . $placeHolder . '_' . $key . ' COLLATE ' . $collate; } // The comparand for this operation is the list of values. $comparand = '(' . implode(', ', $placeHolders) . ')'; } elseif ($criterion->type() == Criterion::CRITERION_TYPE_FIELD) { // Field names should be in a comma separated list. $fList = explode(',', $criterion->value()); foreach (array_keys($fList) as $key) { $fList[$key] = self::getSafeFieldName($fList[$key], $fieldMap) . ' COLLATE ' . $collate; } // The comparand for this operation is the list of field names. $comparand = '(' . implode(', ', $fList) . ')'; } // Escape, quote and qualify the field name for security. $field = self::getSafeFieldName($criterion->key(), $fieldMap); // Build the final clause. // Use end wild-card character for "begins with". $clause->setStatement(self::addLogic($criterion) . ' ' . $field . ' ' . $op . ' ' . $comparand); } return $clause; }
public function inNiniCriterionsProvider() { $inNiniValueAndCriterion = new Criterion(); $inNiniValueAndCriterion->setLogic('and')->setKey('foo')->setOperand('nini')->setType('value')->setValue('abc,def,xyz,123'); $inNiniValueAndClause = new Clause(); $inNiniValueAndClause->setStatement('AND `foo` NOT IN (:filter_0_0 COLLATE utf8_general_ci, :filter_0_1 COLLATE utf8_general_ci, :filter_0_2 COLLATE utf8_general_ci, :filter_0_3 COLLATE utf8_general_ci)')->setParameters(array('filter_0_0' => 'abc', 'filter_0_1' => 'def', 'filter_0_2' => 'xyz', 'filter_0_3' => '123')); $inNiniValueOrCriterion = new Criterion(); $inNiniValueOrCriterion->setLogic('or')->setKey('foo')->setOperand('nini')->setType('value')->setValue('abc,def,xyz,123'); $inNiniValueOrClause = new Clause(); $inNiniValueOrClause->setStatement('OR `foo` NOT IN (:filter_0_0 COLLATE utf8_general_ci, :filter_0_1 COLLATE utf8_general_ci, :filter_0_2 COLLATE utf8_general_ci, :filter_0_3 COLLATE utf8_general_ci)')->setParameters(array('filter_0_0' => 'abc', 'filter_0_1' => 'def', 'filter_0_2' => 'xyz', 'filter_0_3' => '123')); $inNiniTypeAndCriterion = new Criterion(); $inNiniTypeAndCriterion->setLogic('and')->setKey('foo')->setOperand('nini')->setType('field')->setValue('bar,loo,foo'); $inNiniTypeAndClause = new Clause(); $inNiniTypeAndClause->setStatement('AND `foo` NOT IN (`bar` COLLATE utf8_general_ci, `loo` COLLATE utf8_general_ci, `foo` COLLATE utf8_general_ci)'); $inNiniTypeOrCriterion = new Criterion(); $inNiniTypeOrCriterion->setLogic('or')->setKey('foo')->setOperand('nini')->setType('field')->setValue('bar,loo,foo'); $inNiniTypeOrClause = new Clause(); $inNiniTypeOrClause->setStatement('OR `foo` NOT IN (`bar` COLLATE utf8_general_ci, `loo` COLLATE utf8_general_ci, `foo` COLLATE utf8_general_ci)'); return array(array($inNiniValueAndCriterion, $inNiniValueAndClause), array($inNiniValueOrCriterion, $inNiniValueOrClause), array($inNiniTypeAndCriterion, $inNiniTypeAndClause), array($inNiniTypeOrCriterion, $inNiniTypeOrClause)); }