Suitecrm: The V8 API does not allow filtering by custom fields

[ajprouty87]

Issue

When using the version 8 API to list out module entities (for example, accounts) you cannot filter by custom fields.

Expected Behavior

When running a GET request against /api/V8/module/Account?filter[my_custom_field_c][EQ]=MyCustomValue it would return all Account entities where the custom field 'my_custom_field_c' is equal to 'MyCustomValue'.

Actual Behavior

When running a GET request against /api/V8/module/Account?filter[my_custom_field_c][EQ]=MyCustomValue it returns a 400 error along with the detail message of:

Filter field my_custom_field_c in Account module is not found

This happens even through my_custom_field_c was already added as a custom field to the account module.

Possible Fix

Here is the original code that I believe caused this problem:
https://github.com/salesagility/SuiteCRM/blob/475f732cef5b0e02b0f7208446c40b5a985aa406/Api/V8/JsonApi/Repository/Filter.php#L47-L69

You can see that it only checks to see if the field exists on the bean. Custom fields do not get added directly to the bean. The second problem is that the table is hard coded to filter against $bean->getTableName();

If you replace that section of code with the following it seems to be an acceptable solution:

if ( property_exists($bean, $field) ) {
    $tableName = $bean->getTableName(); 
} else if ( $bean->custom_fields->fieldExists($field) ) {
    $tableName = $bean->getTableName() . "_cstm";
} else {
    throw new \InvalidArgumentException(sprintf(
        'Filter field %s in %s module is not found',
        $field,
        $bean->getObjectName()
    ));
}

if (!is_array($expr)) {
    throw new \InvalidArgumentException(sprintf('Filter field %s must be an array', $field));
}

foreach ($expr as $op => $value) {
    $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 switched out the table name into a local variable. If the property exists on the bean it uses the bean table name (previous logic). If it does not exist on the bean, however, it does exist in the custom fields it will add '_cstm' to the table name. This works because the query already automatically adds the custom table to it. If it does not exist in either place it will still throw the exception.

Steps to Reproduce

1. Create a custom field on a module (Accounts for instance).
2. Create an access token through an API request (I use postman).
3. Use that access token to execute a GET request against the module to list module entities while filtering the custom field (For example /api/V8/module/Account?filter[my_custom_field_c][EQ]=MyCustomValue).

Your Environment

• SuiteCRM Version used: 7.10.10

_Update SuiteCRM version._

[helgebe]

I am in the process of using the API as well, and have a related problem (on 7.11.0 and 7.10.11). In my case, your code didn't work, because I don't get a $field, so I get the error message "Filter field in Contacts module is not found." (there is no first %s)
I am not experienced with SuiteCRM, and I haven't done what i want to call a full debug, but after some digging, I ended up trying this:

        foreach ($expr as $op => $value) {
            $this->checkOperator($op);

            if ( substr($field, -2) === "_c" ){
            $where[] = sprintf(
                '%s %s %s',
                $field,
                constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
                $this->db->quoted($value)
            );
            }else{
            $where[] = sprintf(
                '%s.%s %s %s',
                $bean->getTableName(),
                $field,
                constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
                $this->db->quoted($value)
            );
            }
        }

Which is supposed to remove table name from $where if it is a custom field (ends with _c).
Whether this is breaking something else, or is a totally inappropriate way to do it, I don't know, but it works for my testing purposes, and I hope it can help someone else.
In the process I also found something weird in my $bean. There is a ["custom_fields"]=>
NULL there. My custom fields have a ["source"]=>
string(13) "custom_fields".

The original code create a sql error as it tries to do
"where accounts.custom_c = 'value')", and there is no custom fields in account. (error: MySQL error 1054: Unknown column 'accounts.custom_c' in 'where clause')
This also happens on the the count it does before the select.

[cekowu]

I test it in 7.11.4 version, and happened error:
: MySQL error 1054: UNKNOWN COLUMN 'accounts.xx_c' IN 'where clause'

[cekowu]

{
"errors": {
"status": 400,
"title": null,
"detail": "Database failure. Please refer to suitecrm.log for details."
}
}