V8 API: 2nd OR on same field gives only results for 2nd filter

[ebogaard]

Issue

Expected Behavior

When using an OR more than once on the same field, you expect to get results that satisfy both filters.
For example: /V8/module/Accounts?filter[name][eq]=A&filter[operator]=or&filter[name][eq]=B

Should give all Accounts with name A or B.

Actual Behavior

When using this filter, we only get Accounts that satisfy the second filter. The first filter seems to be ignored.

When using an OR on two separate fields, the OR operator works as expected and gives results that satisfy both fitlers.

Possible Fix

n/a

Steps to Reproduce

1. Make sure there is an account called "A" and one called "B"
2. Try API V8 filter twice on same field: /V8/module/Accounts?filter[name][eq]=A&filter[operator]=or&filter[name][eq]=B
3. See that you only get Account "B", while account "A" is expected in the results as well.

Context

Your Environment

• SuiteCRM Version used: 7.12.6
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):
• Environment name and version (e.g. MySQL, PHP 7): PHP 7.4, MariaDB 10.4
• Operating System and version (e.g Ubuntu 16.04): EL 8

[serfreeman1337]

Quick fix to allow array values for requests:

• Replace Api\V8\JsonApi\Repository\Filter.php file with this.
• Example request:
  Api/V8/module/Accounts?filter[operator]=or&filter[name][eq][]=A&filter[name][eq][]=B
  (note the [])

[ebogaard]

Ah, thanks. So this is just an omission in the documentation, not a functional issue.

[serfreeman1337]

Current API filter design doesn't allow to specify same key name twice.
Proposed quick fix should at least allow that, but it's not official and any such changes to files migh be overriten during suitecrm update.

[ebogaard]

Sorry, didn't read that correctly. Didn't see a patch is necessary. Seems like a proper way to fix this, though.

[SuiteBot]

This issue has been mentioned on SuiteCRM. There might be relevant details there:

https://community.suitecrm.com/t/retrieve-api-v8-data-with-or-and-and-operator/86149/2

[TwizzX17]

Since the link to the previous proposed solution doesn't work anymore, have i tried to recreate the solution from the description:

Api/V8/JsonApi/Repository/Filter.php:75

foreach ($expr as $op => $value) {
    /* NEW CODE BLOCK - if $value is an array */
    if (is_array($value)) {
	foreach ($value as $key => $embedded_value) {
		$this->checkOperator($op);
		$where[] = sprintf(
			'%s.%s %s %s',
			$tableName,
			$field,
			constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
			$this->db->quoted($embedded_value)
		);
	}
	continue;
    }

    $this->checkOperator($op);
    $where[] = sprintf(
        '%s.%s %s %s',
        $tableName,
        $field,
        constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
        $this->db->quoted($value)
    );
}

I've added code to the parseWhere function, into the original foreach loop of $expres, to handle it a different way, if the $value is of type array. Whether this is the correct solution for the project, am i not sure of, but if anyone else needs a quick fix for this issue, then here is an example.

As described earlier in this thread - design your params with empty brackets like this:

• Api/V8/module/Accounts?filter[operator]=or&filter[name][eq][]=A&filter[name][eq][]=B
  (note the [])