V8 API: unable to filter on custom fields

[Lehnerr]

Issue

I've requesting a Contact with filtering on a custom field. I get database error as result

Expected Behavior

I'm expecting that the filter is also working on custom fields and not only on standard fields

Actual Behavior

Request: https://DOMAIN/crm/Api/V8/module/Contacts?fields[Contacts]=id,name,first_name,last_name,phone_mobile,phone_work&filter[operator]=or&filter[ctx_mobile_c][eq]=00439999999

Ends in SQLError:

Thu Feb 3 19:10:17 2022 [62233][1][FATAL] Mysqli_query failed. Thu Feb 3 19:10:17 2022 [62233][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM contacts WHERE (contacts.ctx_mobile_c = '00436642304526') AND contacts.deleted = '0': MySQL error 1054: Unknown column 'contacts.ctx_mobile_c' in 'where clause' T

Possible Fix

correct query to use join _cstm Table

Steps to Reproduce

1.create custom filed in contacts 2.create contact and fill custom field. 3.query the API 4.

Context

Your Environment

• SuiteCRM Version used: 7.12.2
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):
• Environment name and version (e.g. MySQL, PHP 7): php7.3
• Operating System and version (e.g Ubuntu 16.04): ubuntu 20

[Vagos91]

Same issue here in 7.11.22, any way for a temporary fix? Tried some solutions found in older threads but nothing worked, it keeps searching in the non _cstm table.

Edit: Updated to 7.12.3, not fixed. Still no solution seems to work for me.

[serfreeman1337]

Already fixed as of v7.12.0 and v7.11.20. Related issue https://github.com/salesagility/SuiteCRM/issues/7285 . You should replace your Api/V8/JsonApi/Repository/Filter.php file from the hotfix branch to have both OR and custom fields filters fixed.

[holdusback]

I still have my issue in 7.12.5 -> Still no fix on this ?

I got this exact same error, but the API filter work lol

Im filtering a field in a form to check if the client is already in the database of suitecrm, the filter work like a charm, it just every call create error in the log …

The call look like Api/V8/module/Accounts?filter[siren_c][eq]=‘XXXX’

In generate in suitecrm.log : Tue Apr 5 12:14:29 2022 [71911][1][FATAL] Mysqli_query failed. Tue Apr 5 12:14:29 2022 [71911][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM accounts WHERE (accounts_cstm.siren_c = ‘XXXX’) AND accounts.deleted = ‘0’: MySQL error 1054: Unknown column ‘accounts_cstm.siren_c’ in ‘where clause’

The error are created everytime, if the filter return value or not. So its pretty much a bug

So its not really a big issue but this form is used a lot so it generate a bunch of useless line in the suitecrm.log.

I hope someone can help, if needed I will create an issue

[holdusback]

Just as an update... This error still happen 7.12.7 I have request to the API everyday, so this make my log grow up insanely fast, the thing is that the API filter work well, I don't even know what this error mean.

[serfreeman1337]

Try to replace countRecords function in the Api/V8/BeanDecorator/BeanManager.php file with this:

    public function countRecords($module, $where)
    {
        $bean = $this->newBeanSafe($module);
        $table = $bean->getTableName();
        $customTable = $bean->get_custom_table_name();

        if (strpos($where, $customTable) !== FALSE) {
            $table .= sprintf(" LEFT JOIN %s ON %s.id = %s.id_c", $customTable, $table, $customTable);
        }

        $rowCount = $this->db->fetchRow(
            $this->db->query(
                sprintf(
                    "SELECT COUNT(*) AS cnt FROM %s %s",
                    $table,
                    $where === '' ? '' : 'WHERE ' .  $where
                )
            )
        )["cnt"];

        return (int)$rowCount;
    }

[holdusback]

Try to replace countRecords function in the Api/V8/BeanDecorator/BeanManager.php file with this:

    public function countRecords($module, $where)
    {
        $bean = $this->newBeanSafe($module);
        $table = $bean->getTableName();
        $customTable = $bean->get_custom_table_name();

        if (strpos($where, $customTable) !== FALSE) {
            $table .= sprintf(" LEFT JOIN %s ON %s.id = %s.id_c", $customTable, $table, $customTable);
        }

        $rowCount = $this->db->fetchRow(
            $this->db->query(
                sprintf(
                    "SELECT COUNT(*) AS cnt FROM %s %s",
                    $table,
                    $where === '' ? '' : 'WHERE ' .  $where
                )
            )
        )["cnt"];

        return (int)$rowCount;
    }

Thank you ! Look like I dont have anymore the error in my log. So it look like to fix my (this ?) issue.

Since its not upgrade safe, I will have to make this change on every update right ? IDK if they will merge your fix.

Anyway thanks a lot !