'name' => 'data_type',
'data_type' => 'String',
'options' => [
- 'Integer' => ts('Integer'),
+ 'Array' => ts('Array'),
'Boolean' => ts('Boolean'),
+ 'Date' => ts('Date'),
+ 'Float' => ts('Float'),
+ 'Integer' => ts('Integer'),
'String' => ts('String'),
'Text' => ts('Text'),
- 'Date' => ts('Date'),
'Timestamp' => ts('Timestamp'),
- 'Array' => ts('Array'),
],
],
[
'name' => 'input_type',
'data_type' => 'String',
'options' => [
- 'Text' => ts('Text'),
- 'Number' => ts('Number'),
- 'Select' => ts('Select'),
+ 'ChainSelect' => ts('ChainSelect'),
'CheckBox' => ts('CheckBox'),
- 'Radio' => ts('Radio'),
'Date' => ts('Date'),
- 'File' => ts('File'),
'EntityRef' => ts('EntityRef'),
- 'ChainSelect' => ts('ChainSelect'),
+ 'File' => ts('File'),
+ 'Number' => ts('Number'),
+ 'Radio' => ts('Radio'),
+ 'Select' => ts('Select'),
+ 'Text' => ts('Text'),
],
],
[
case 'IS NOT NULL':
return is_null($value) == ($operator == 'IS NULL');
+ case 'IS EMPTY':
+ case 'IS NOT EMPTY':
+ return empty($value) == ($operator == 'IS EMPTY');
+
case '>':
return $value > $expected;
if ($fieldName && $valExpr->getType() === 'SqlString') {
$value = $valExpr->getExpr();
FormattingUtil::formatInputValue($value, $fieldName, $this->apiFieldSpec[$fieldName], $operator);
- return \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
+ return $this->createSQLClause($fieldAlias, $operator, $value, $this->apiFieldSpec[$fieldName]);
}
else {
$value = $valExpr->render($this->apiFieldSpec);
}
}
+ $sqlClause = $this->createSQLClause($fieldAlias, $operator, $value, $field ?? NULL);
+ if ($sqlClause === NULL) {
+ throw new \API_Exception("Invalid value in $type clause for '$expr'");
+ }
+ return $sqlClause;
+ }
+
+ /**
+ * @param string $fieldAlias
+ * @param string $operator
+ * @param mixed $value
+ * @param array|null $field
+ * @return array|string|NULL
+ * @throws \Exception
+ */
+ protected function createSQLClause($fieldAlias, $operator, $value, $field) {
if ($operator === 'CONTAINS') {
switch ($field['serialize'] ?? NULL) {
case \CRM_Core_DAO::SERIALIZE_JSON:
}
}
- $sql_clause = \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
- if ($sql_clause === NULL) {
- throw new \API_Exception("Invalid value in $type clause for '$expr'");
+ if ($operator === 'IS EMPTY' || $operator === 'IS NOT EMPTY') {
+ // If field is not a string or number, this will pass through and use IS NULL/IS NOT NULL
+ $operator = str_replace('EMPTY', 'NULL', $operator);
+ // For strings & numbers, create an OR grouping of empty value OR null
+ if (in_array($field['data_type'] ?? NULL, ['String', 'Integer', 'Float'], TRUE)) {
+ $emptyVal = $field['data_type'] === 'String' ? '""' : '0';
+ $isEmptyClause = $operator === 'IS NULL' ? "= $emptyVal OR" : "<> $emptyVal AND";
+ return "($fieldAlias $isEmptyClause $fieldAlias $operator)";
+ }
}
- return $sql_clause;
+
+ return \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
}
/**
public static function getOperators() {
$operators = \CRM_Core_DAO::acceptedSQLOperators();
$operators[] = 'CONTAINS';
+ $operators[] = 'IS EMPTY';
+ $operators[] = 'IS NOT EMPTY';
return $operators;
}
// Add/remove value if operator allows for one
this.changeClauseOperator = function(clause) {
- if (_.contains(clause[1], 'NULL')) {
+ if (_.contains(clause[1], 'IS ')) {
clause.length = 2;
} else if (clause.length === 2) {
clause.push('');
op = field.serialize || dataType === 'Array' ? 'IN' : '=';
}
multi = _.includes(['IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'], op);
- if (op === 'IS NULL' || op === 'IS NOT NULL') {
+ // IS NULL, IS EMPTY, etc.
+ if (_.contains(op, 'IS ')) {
$el.hide();
return;
}
'NOT LIKE' => E::ts('Not Like'),
'BETWEEN' => E::ts('Is Between'),
'NOT BETWEEN' => E::ts('Not Between'),
- 'IS NULL' => E::ts('Is Null'),
- 'IS NOT NULL' => E::ts('Not Null'),
+ 'IS EMPTY' => E::ts('Is Empty'),
+ 'IS NOT EMPTY' => E::ts('Not Empty'),
];
}
}
};
+ // Returns false for 'IS NULL', 'IS EMPTY', etc. true otherwise.
+ this.operatorTakesInput = function(operator) {
+ return operator.indexOf('IS ') !== 0;
+ };
+
this.changeClauseOperator = function(clause) {
- // Add/remove value if operator allows for one
- if (_.contains(clause[1], 'NULL')) {
+ // Add/remove value depending on whether operator allows for one
+ if (!ctrl.operatorTakesInput(clause[1])) {
clause.length = 2;
} else {
if (clause.length === 2) {
<div ng-if="!$ctrl.conjunctions[clause[0]]" class="api4-input-group">
<input class="form-control" ng-model="clause[0]" crm-ui-select="{data: $ctrl.fields, allowClear: true, placeholder: 'Field'}" ng-change="$ctrl.changeClauseField(clause, index)" />
<select class="form-control api4-operator" ng-model="clause[1]" ng-options="o.key as o.value for o in $ctrl.operators" ng-change="$ctrl.changeClauseOperator(clause)" ></select>
- <crm-search-input ng-if="clause[1] !== 'IS NULL' && clause[1] !== 'IS NOT NULL'" ng-model="clause[2]" field="$ctrl.getField(clause[0])" option-key="$ctrl.getOptionKey(clause[0])" op="clause[1]" format="$ctrl.format" class="form-group"></crm-search-input>
+ <crm-search-input ng-if="$ctrl.operatorTakesInput(clause[1])" ng-model="clause[2]" field="$ctrl.getField(clause[0])" option-key="$ctrl.getOptionKey(clause[0])" op="clause[1]" format="$ctrl.format" class="form-group"></crm-search-input>
</div>
<fieldset class="clearfix" ng-if="$ctrl.conjunctions[clause[0]]">
<crm-search-clause clauses="clause[1]" format="{{ $ctrl.format }}" op="{{ clause[0] }}" fields="$ctrl.fields" delete-group="$ctrl.deleteRow(index)" ></crm-search-clause>
*/
class BasicActionsTest extends UnitTestCase {
+ private function replaceRecords(&$records) {
+ MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
+ foreach ($records as &$record) {
+ $record['id'] = MockBasicEntity::create()->setValues($record)->execute()->first()['id'];
+ }
+ }
+
public function testCrud() {
MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
}
public function testReplace() {
- MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
-
$objects = [
['group' => 'one', 'color' => 'red'],
['group' => 'one', 'color' => 'blue'],
['group' => 'two', 'color' => 'orange'],
];
- foreach ($objects as &$object) {
- $object['id'] = MockBasicEntity::create()->setValues($object)->execute()->first()['id'];
- }
+ $this->replaceRecords($objects);
// Keep red, change blue, delete green, and add yellow
$replacements = [
}
public function testBatchFrobnicate() {
- MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
-
$objects = [
['group' => 'one', 'color' => 'red', 'number' => 10],
['group' => 'one', 'color' => 'blue', 'number' => 20],
['group' => 'one', 'color' => 'green', 'number' => 30],
['group' => 'two', 'color' => 'blue', 'number' => 40],
];
- foreach ($objects as &$object) {
- $object['id'] = MockBasicEntity::create()->setValues($object)->execute()->first()['id'];
- }
+ $this->replaceRecords($objects);
$result = MockBasicEntity::batchFrobnicate()->addWhere('color', '=', 'blue')->execute();
$this->assertEquals(2, count($result));
}
public function testWildcardSelect() {
- MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
-
$records = [
['group' => 'one', 'color' => 'red', 'shape' => 'round', 'size' => 'med', 'weight' => 10],
['group' => 'two', 'color' => 'blue', 'shape' => 'round', 'size' => 'med', 'weight' => 20],
];
- MockBasicEntity::save()->setRecords($records)->execute();
+ $this->replaceRecords($records);
foreach (MockBasicEntity::get()->addSelect('*')->execute() as $result) {
ksort($result);
$this->assertEquals(['shape', 'size', 'weight'], array_keys($result));
}
- public function testContainsOperator() {
- MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
+ public function testEmptyAndNullOperators() {
+ $records = [
+ [],
+ ['color' => '', 'weight' => 0],
+ ['color' => 'yellow', 'weight' => 100000000000],
+ ];
+ $this->replaceRecords($records);
+
+ $result = MockBasicEntity::get()
+ ->addWhere('color', 'IS NULL')
+ ->execute()->indexBy('id');
+ $this->assertCount(1, $result);
+ $this->assertArrayHasKey($records[0]['id'], (array) $result);
+ $result = MockBasicEntity::get()
+ ->addWhere('color', 'IS EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(2, $result);
+ $this->assertArrayNotHasKey($records[2]['id'], (array) $result);
+
+ $result = MockBasicEntity::get()
+ ->addWhere('color', 'IS NOT EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(1, $result);
+ $this->assertArrayHasKey($records[2]['id'], (array) $result);
+
+ $result = MockBasicEntity::get()
+ ->addWhere('weight', 'IS NULL')
+ ->execute()->indexBy('id');
+ $this->assertCount(1, $result);
+ $this->assertArrayHasKey($records[0]['id'], (array) $result);
+
+ $result = MockBasicEntity::get()
+ ->addWhere('weight', 'IS EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(2, $result);
+ $this->assertArrayNotHasKey($records[2]['id'], (array) $result);
+
+ $result = MockBasicEntity::get()
+ ->addWhere('weight', 'IS NOT EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(1, $result);
+ $this->assertArrayHasKey($records[2]['id'], (array) $result);
+ }
+
+ public function testContainsOperator() {
$records = [
['group' => 'one', 'fruit:name' => ['apple', 'pear'], 'weight' => 11],
['group' => 'two', 'fruit:name' => ['pear', 'banana'], 'weight' => 12],
];
- MockBasicEntity::save()->setRecords($records)->execute();
+ $this->replaceRecords($records);
$result = MockBasicEntity::get()
->addWhere('fruit:name', 'CONTAINS', 'apple')
}
public function testPseudoconstantMatch() {
- MockBasicEntity::delete()->addWhere('id', '>', 0)->execute();
-
$records = [
['group:label' => 'First', 'shape' => 'round', 'fruit:name' => 'banana'],
['group:name' => 'Second', 'shape' => 'square', 'fruit:label' => 'Pear'],
];
- MockBasicEntity::save()->setRecords($records)->execute();
+ $this->replaceRecords($records);
$results = MockBasicEntity::get()
->addSelect('*', 'group:label', 'group:name', 'fruit:name', 'fruit:color', 'fruit:label')
}
}
+ public function testEmptyAndNullOperators() {
+ $last_name = uniqid(__FUNCTION__);
+
+ $bob = Contact::create()
+ ->setValues(['first_name' => 'Bob', 'last_name' => $last_name, 'prefix_id' => 0])
+ ->execute()->first();
+ // Initial value is NULL, but to test the empty operator, change it to an empty string
+ \CRM_Core_DAO::executeQuery("UPDATE civicrm_contact SET middle_name = '' WHERE id = " . $bob['id']);
+
+ $jan = Contact::create()
+ ->setValues(['first_name' => 'Jan', 'middle_name' => 'J', 'last_name' => $last_name, 'prefix_id' => 1])
+ ->execute()->first();
+
+ $dan = Contact::create()
+ ->setValues(['first_name' => 'Dan', 'last_name' => $last_name, 'prefix_id' => NULL])
+ ->execute()->first();
+
+ // Test EMPTY and NULL operators on string fields
+ $result = Contact::get(FALSE)
+ ->addWhere('last_name', '=', $last_name)
+ ->addWhere('middle_name', 'IS EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(2, $result);
+ $this->assertArrayNotHasKey($jan['id'], (array) $result);
+
+ $result = Contact::get(FALSE)
+ ->addWhere('last_name', '=', $last_name)
+ ->addWhere('middle_name', 'IS NOT NULL')
+ ->execute()->indexBy('id');
+ $this->assertCount(2, $result);
+ $this->assertArrayNotHasKey($dan['id'], (array) $result);
+
+ $result = Contact::get(FALSE)
+ ->addWhere('last_name', '=', $last_name)
+ ->addWhere('middle_name', 'IS NOT EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(1, $result);
+ $this->assertArrayHasKey($jan['id'], (array) $result);
+
+ // Test EMPTY and NULL operators on Integer fields
+ $result = Contact::get(FALSE)
+ ->addWhere('last_name', '=', $last_name)
+ ->addWhere('prefix_id', 'IS EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(2, $result);
+ $this->assertArrayNotHasKey($jan['id'], (array) $result);
+
+ $result = Contact::get(FALSE)
+ ->addWhere('last_name', '=', $last_name)
+ ->addWhere('prefix_id', 'IS NOT NULL')
+ ->execute()->indexBy('id');
+ $this->assertCount(2, $result);
+ $this->assertArrayNotHasKey($dan['id'], (array) $result);
+
+ $result = Contact::get(FALSE)
+ ->addWhere('last_name', '=', $last_name)
+ ->addWhere('prefix_id', 'IS NOT EMPTY')
+ ->execute()->indexBy('id');
+ $this->assertCount(1, $result);
+ $this->assertArrayHasKey($jan['id'], (array) $result);
+ }
+
}
projects / civicrm-core.git / commitdiff