starred/SuiteCRM-SuiteCRM

Fork 0

mirror of https://github.com/SuiteCRM/SuiteCRM.git synced 2026-03-02 19:16:58 -05:00

V8 API: unable to filter on custom fields #4726

New issue

Open

opened 2026-02-20 16:22:47 -05:00 by deekerman · 6 comments

deekerman commented

2026-02-20 16:22:47 -05:00

Owner

Originally created by @Lehnerr on GitHub (Feb 3, 2022).

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

Originally created by @Lehnerr on GitHub (Feb 3, 2022).    #### 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

deekerman added the

Type: Bug

Priority:Important

Area: API

labels

2026-02-20 16:22:47 -05:00

deekerman commented

2026-02-20 16:22:49 -05:00

Author

Owner

@Vagos91 commented on GitHub (Feb 5, 2022):

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.

@Vagos91 commented on GitHub (Feb 5, 2022): 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.

deekerman commented

2026-02-20 16:22:50 -05:00

Author

Owner

@serfreeman1337 commented on GitHub (Mar 1, 2022):

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.

@serfreeman1337 commented on GitHub (Mar 1, 2022): 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](https://raw.githubusercontent.com/salesagility/SuiteCRM/hotfix/Api/V8/JsonApi/Repository/Filter.php ) to have both [OR](https://github.com/salesagility/SuiteCRM/pull/8492) and [custom fields](https://github.com/salesagility/SuiteCRM/pull/8731) filters fixed.

deekerman commented

2026-02-20 16:22:50 -05:00

Author

Owner

@holdusback commented on GitHub (Apr 5, 2022):

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 commented on GitHub (Apr 5, 2022): 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

deekerman commented

2026-02-20 16:22:50 -05:00

Author

Owner

@holdusback commented on GitHub (Aug 31, 2022):

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.

@holdusback commented on GitHub (Aug 31, 2022): 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.

deekerman commented

2026-02-20 16:22:50 -05:00

Author

Owner

@serfreeman1337 commented on GitHub (Aug 31, 2022):

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;
    }

@serfreeman1337 commented on GitHub (Aug 31, 2022): Try to replace [countRecords function](https://github.com/salesagility/SuiteCRM/blob/7b6ac1bb788bf0904e05cea6d9069a5feeaab52f/Api/V8/BeanDecorator/BeanManager.php#L246) in the **Api/V8/BeanDecorator/BeanManager.php** file with this: ```php 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; } ```

deekerman commented

2026-02-20 16:22:50 -05:00

Author

Owner

@holdusback commented on GitHub (Sep 1, 2022):

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 !

@holdusback commented on GitHub (Sep 1, 2022): > Try to replace [countRecords function](https://github.com/salesagility/SuiteCRM/blob/7b6ac1bb788bf0904e05cea6d9069a5feeaab52f/Api/V8/BeanDecorator/BeanManager.php#L246) 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 !