7.11.4,Database failure when filter custom fields by V8 API #7285

cekowu · 2019-05-15T00:40:00Z

Issue

Expected Behavior

Actual Behavior

Possible Fix

Steps to Reproduce

Upgrade to 7.11.4 version
Filter custom fields by V8 API , etg:
SuiteCRM/Api/V8/module/Accounts?filter[operator]=and&filter[xx_c][eq]=431565
Response:
{ "errors": { "status": 400, "title": null, "detail": "Database failure. Please refer to suitecrm.log for details." } }
check debug log:
: MySQL error 1054: UNKNOWN COLUMN 'accounts.xxx_c' IN 'where clause'
SELECT accounts.*, accounts_cstm.jjwg_maps_lng_c, accounts_cstm.jjwg_maps_lat_c, accounts_cstm.jjwg_maps_geocode_status_c, accounts_cstm.jjwg_maps_address_c, accounts_cstm.cust_full_name_c, accounts_cstm.domain_c, accounts_cstm.egdelivery_c, accounts_cstm.egselecteddistributor_c, accounts_cstm.groupemail_c, accounts_cstm.hpe_dba_cust_name_c, accounts_cstm.hpe_fse_c, accounts_cstm.hpe_isr_c, accounts_cstm.hpe_legal_name_c, accounts_cstm.hp_loc_id_c, accounts_cstm.hybriditspecialist_c, accounts_cstm.mastercustno_c, accounts_cstm.pr_directresponse_c, accounts_cstm.pr_eg_service_provider_spec_c, accounts_cstm.pr_ntwkg_wirelesslan_spec_c, accounts_cstm.pr_ntwrk_campus_switch_c, accounts_cstm.pr_service_specialist_c, accounts_cstm.snx_cust_no_c, accounts_cstm.snx_sales_terr_c, accounts_cstm.verticalmkt_c, jt0.user_name modified_by_name, jt0.created_by modified_by_name_owner, 'Users' modified_by_name_mod, jt1.user_name created_by_name, jt1.created_by created_by_name_owner, 'Users' created_by_name_mod, jt2.user_name AS signed_user_name, jt2.created_by AS signed_user_name_owner, 'Users' AS signed_user_name_mod, jt3.name parent_name, jt3.assigned_user_id parent_name_owner, 'Accounts' parent_name_mod, jt4.name campaign_name, jt4.assigned_user_id campaign_name_owner, 'Campaigns' campaign_name_mod FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id = jt0.id AND jt0.deleted = 0 AND jt0.deleted = 0 LEFT JOIN users jt1 ON accounts.created_by = jt1.id AND jt1.deleted = 0 AND jt1.deleted = 0 LEFT JOIN users jt2 ON accounts.assigned_user_id = jt2.id AND jt2.deleted = 0 AND jt2.deleted = 0 LEFT JOIN accounts jt3 ON accounts.parent_id = jt3.id AND jt3.deleted = 0 AND jt3.deleted = 0 LEFT JOIN campaigns jt4 ON accounts.campaign_id = jt4.id AND jt4.deleted = 0 AND jt4.deleted = 0 WHERE (accounts.xx_c = '431565' AND accounts.deleted = '0') AND accounts.deleted = 0 LIMIT 0,50;

Context

Your Environment

SuiteCRM Version used:7.11.4
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):
Environment name and version (e.g. MySQL, PHP 7):
Operating System and version (e.g Ubuntu 16.04):

The text was updated successfully, but these errors were encountered:

cekowu · 2019-05-15T00:45:13Z

I check #6455 , this issue had been fixed , but i test it and don't work, I don't understand it.

cameronblaikie · 2019-06-07T11:36:45Z

Hi there,

I could not replicate this, is there any additional information you could provide which could lead to us replicating this so we can get this fix for your issue.

Thanks,

nathanle · 2019-06-09T04:12:08Z

SuiteCRM version 7.11.5

Request: https://crminstance.com/CRM/Api/V8/module/Accounts?fields[Accounts]=billingcity_c&filter[operator]=and&filter[billingcity_c][eq]=Seattle

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

Logs:
Sun Jun 9 04:07:50 2019 [25793][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM accounts WHERE accounts.billingcity_c = 'Seattle' AND accounts.deleted = '0': MySQL error 1054: Unknown column 'accounts.billingcity_c' in 'where clause'

nathanle · 2019-06-11T04:17:01Z

AND jt4.deleted=0 LEFT JOIN accounts jt5 ON accounts_cstm.account_id_c = jt5.id AND jt5.deleted=0 LEFT JOIN accounts jt6 ON accounts_cstm.account_id_c = jt6.id AND jt6.deleted=0 where (accounts.billingcity_c = Seattle' AND accounts.deleted = '0') AND accounts.deleted=0: MySQL error 1054: Unknown column 'accounts.billingcity_c' in 'where clause'

As written from the logs:
mysql> SELECT count(*) c FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0 -> -> AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0 -> -> AND jt1.deleted=0 LEFT JOIN users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0 -> -> AND jt2.deleted=0 LEFT JOIN accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0 -> -> AND jt3.deleted=0 LEFT JOIN campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0 -> -> AND jt4.deleted=0 LEFT JOIN accounts jt5 ON accounts_cstm.account_id_c = jt5.id AND jt5.deleted=0 LEFT JOIN accounts jt6 ON accounts_cstm.account_id_c = jt6.id AND jt6.deleted=0 where (accounts.billingcity_c = 'Alpharetta' AND accounts.deleted = '0') AND accounts.deleted=0; ERROR 1054 (42S22): Unknown column 'accounts.billingcity_c' in 'where clause'

Corrected:
mysql> SELECT count(*) c FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 LEFT JOIN users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0 AND jt2.deleted=0 LEFT JOIN accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0 AND jt3.deleted=0 LEFT JOIN campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0 AND jt4.deleted=0 LEFT JOIN accounts jt5 ON accounts_cstm.account_id_c = jt5.id AND jt5.deleted=0 LEFT JOIN accounts jt6 ON accounts_cstm.account_id_c = jt6.id AND jt6.deleted=0 where (accounts_cstm.billingcity_c = 'Alpharetta' AND accounts.deleted = '0') AND accounts.deleted=0;
+-----+
| c |
+-----+
| 377 |
+-----+
1 row in set (0.00 sec)

nathanle · 2019-06-11T04:29:46Z

Another example from the logs.
From the logs - broken:
mysql> SELECT accounts.* ,accounts_cstm.ownerid_c,accounts_cstm.lastmodifiedbyid_c,accounts_cstm.address2__c_country_c,accounts_cstm.lastactivitydate_c,accounts_cstm.phone_c,accounts_cstm.industry_c,accounts_cstm.isdeleted_c,accounts_cstm.isexcludedfromrealign_c,accounts_cstm.shippingcity_c,accounts_cstm.altphone__c,accounts_cstm.cell_mobile__c,accounts_cstm.billingcountry_c,accounts_cstm.address2__c_state_c,accounts_cstm.shippingpostalcode_c,accounts_cstm.parentid_c,accounts_cstm.salesforceparentid_c,accounts_cstm.mclabs2__count_of_properties_c,accounts_cstm.accountsource_c,accounts_cstm.createddate_c,accounts_cstm.address2__c,accounts_cstm.mclabs2__osf_id__c,accounts_cstm.systemmodstamp_c,accounts_cstm.address2__c_city_c,accounts_cstm.salesforceid_c,accounts_cstm.extention__c,accounts_cstm.account_id_c,accounts_cstm.group__c,accounts_cstm.billingpostalcode_c,accounts_cstm.fax_c,accounts_cstm.billingstate_c,accounts_cstm.createdbyid_c,accounts_cstm.lastmodifieddate_c,accounts_cstm.address2__c_postalcode_c,accounts_cstm.category__c,accounts_cstm.sub_market__c,accounts_cstm.type_c,accounts_cstm.shippingstreet_c,accounts_cstm.recordtypeid_c,accounts_cstm.createuserid__c,accounts_cstm.billingstreet_c,accounts_cstm.billingcity_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod , jt3.name parent_name , jt3.assigned_user_id parent_name_owner , 'Accounts' parent_name_mod , jt4.name campaign_name , jt4.assigned_user_id campaign_name_owner , 'Campaigns' campaign_name_mod, jt5.name parent2_c , jt6.name parent_c FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0 -> -> AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0 -> -> AND jt1.deleted=0 LEFT JOIN users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0 -> -> AND jt2.deleted=0 LEFT JOIN accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0 -> -> AND jt3.deleted=0 LEFT JOIN campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0 -> -> AND jt4.deleted=0 LEFT JOIN accounts jt5 ON accounts_cstm.account_id_c = jt5.id AND jt5.deleted=0 LEFT JOIN accounts jt6 ON accounts_cstm.account_id_c = jt6.id AND jt6.deleted=0 where (accounts.billingcity_c = 'Alpharetta' AND accounts.deleted = '0') AND accounts.deleted=0; ERROR 1054 (42S22): Unknown column 'accounts.billingcity_c' in 'where clause'
Changed:
mysql> SELECT accounts.* ,accounts_cstm.ownerid_c,accounts_cstm.lastmodifiedbyid_c,accounts_cstm.address2__c_country_c,accounts_cstm.lastactivitydate_c,accounts_cstm.phone_c,accounts_cstm.industry_c,accounts_cstm.isdeleted_c,accounts_cstm.isexcludedfromrealign_c,accounts_cstm.shippingcity_c,accounts_cstm.altphone__c,accounts_cstm.cell_mobile__c,accounts_cstm.billingcountry_c,accounts_cstm.address2__c_state_c,accounts_cstm.shippingpostalcode_c,accounts_cstm.parentid_c,accounts_cstm.salesforceparentid_c,accounts_cstm.mclabs2__count_of_properties_c,accounts_cstm.accountsource_c,accounts_cstm.createddate_c,accounts_cstm.address2__c,accounts_cstm.mclabs2__osf_id__c,accounts_cstm.systemmodstamp_c,accounts_cstm.address2__c_city_c,accounts_cstm.salesforceid_c,accounts_cstm.extention__c,accounts_cstm.account_id_c,accounts_cstm.group__c,accounts_cstm.billingpostalcode_c,accounts_cstm.fax_c,accounts_cstm.billingstate_c,accounts_cstm.createdbyid_c,accounts_cstm.lastmodifieddate_c,accounts_cstm.address2__c_postalcode_c,accounts_cstm.category__c,accounts_cstm.sub_market__c,accounts_cstm.type_c,accounts_cstm.shippingstreet_c,accounts_cstm.recordtypeid_c,accounts_cstm.createuserid__c,accounts_cstm.billingstreet_c,accounts_cstm.billingcity_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod , jt3.name parent_name , jt3.assigned_user_id parent_name_owner , 'Accounts' parent_name_mod , jt4.name campaign_name , jt4.assigned_user_id campaign_name_owner , 'Campaigns' campaign_name_mod, jt5.name parent2_c , jt6.name parent_c FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 LEFT JOIN users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0 AND jt2.deleted=0 LEFT JOIN accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0 AND jt3.deleted=0 LEFT JOIN campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0 AND jt4.deleted=0 LEFT JOIN accounts jt5 ON accounts_cstm.account_id_c = jt5.id AND jt5.deleted=0 LEFT JOIN accounts jt6 ON accounts_cstm.account_id_c = jt6.id AND jt6.deleted=0 where (accounts_cstm.billingcity_c = 'Alpharetta' AND accounts.deleted = '0') AND accounts.deleted=0;
...results...
377 rows in set (0.01 sec)

nathanle · 2019-06-13T01:13:10Z

I tested a few things, and I am fairly confident that, in this case, "accounts_cstm" should be returned by $bean->getTableName() on line 66 of Filter.php for this to work correctly. I am just unsure why it is not happening.

danvasem · 2019-06-22T01:49:42Z

Today I found the same error on Contacts module trying to filter on a custom field. Also, I've got the same conclussion as @nathanle about where the error should be fixed. ¿Is there a plan to fix it on next release?

isleshocky77 · 2019-07-10T19:44:34Z

I just bumped up against this issue on the "Leads" module while attempting to filter on a custom field. @Dillon-Brown @cameronblaikie What are you looking for as far as information?

Dillon-Brown · 2019-07-11T14:22:12Z

This should be plenty of information to go on, marked as a bug. Thanks!

cduquev · 2019-10-23T20:36:52Z

@Dillon-Brown @cameronblaikie I'm still having the same issue. Is there any plans to solve this

jan-klima · 2019-10-27T13:41:39Z

@Dillon-Brown @cameronblaikie I'm still having the same issue. Is there any plans to solve this

Same here :-( I can confirm the bug is still present on 7.11.8 - I have it on 2 separate installations. Just tested it few mins ago:

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

theerapatkij · 2019-11-08T07:32:18Z

@cduquev @jan-waterdrop
Still having same issue too. Anyone has any solution or any update?

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

ghost · 2019-11-14T19:33:18Z

Can someone confirm if there is a commit to this issue that has fixed it?

pgorod · 2019-11-15T22:45:33Z

@ahmed-cader I don't see any fix related to this issue, probably there is none... :-(

artjomsmorscakovs · 2019-12-09T01:19:35Z

Not a very proper solution, but a quick and very dirty workaround:
/Api/V8/JsonApi/Repository/Filter.php
Line: 68 replace: $bean->getTableName(),
with $this->isCustomTable($field, $bean->getTableName()),

add some helper functions in the same file
` private function isCustomTable($field, $table){
if($this->endsWith($field, "_c")) {
$table = $table.'_cstm';
}

	return $table;
}

private function endsWith($haystack, $needle){
    $length = strlen($needle);
    if ($length == 0) {
        return true;
    }

    return (substr($haystack, -$length) === $needle);
}`

And wait for a proper fix

Lehnerr · 2020-01-14T17:02:34Z

The dirty workaround solves my life in 7.11.9. Pls. resolve the issue

pgorod · 2020-01-14T17:15:39Z

Why is the fix called "dirty", what is it lacking to be a "proper fix"? I'm just asking because if nobody makes the PR this will likely get stalled...

artjomsmorscakovs · 2020-01-18T16:46:56Z

Why is the fix called "dirty", what is it lacking to be a "proper fix"? I'm just asking because if nobody makes the PR this will likely get stalled...

Because I don`t know the impact.
This "dirty" solution might become a proper solution as well, at least for me it solves the issue

Lehnerr · 2020-01-19T09:23:11Z

One impact I've seen. When using filter with custom field and page options, the total amount pages is always 0.

suneelIBMkummar · 2020-01-30T06:17:16Z

Not a very proper solution, but a quick and very dirty workaround:
/Api/V8/JsonApi/Repository/Filter.php
Line: 68 replace: $bean->getTableName(),
with $this->isCustomTable($field, $bean->getTableName()),

add some helper functions in the same file
` private function isCustomTable($field, $table){
if($this->endsWith($field, "_c")) {
$table = $table.'_cstm';
}
	return $table;
}

private function endsWith($haystack, $needle){
    $length = strlen($needle);
    if ($length == 0) {
        return true;
    }

    return (substr($haystack, -$length) === $needle);
}`
And wait for a proper fix

Works like a charm! Thank You!!!!

pgorod · 2020-01-30T09:45:25Z

Can anyone mitigate the impact the @Lehnerr mentioned above?

rpinzon · 2020-04-13T02:24:19Z

Not a very proper solution, but a quick and very dirty workaround:
/Api/V8/JsonApi/Repository/Filter.php
Line: 68 replace: $bean->getTableName(),
with $this->isCustomTable($field, $bean->getTableName()),

add some helper functions in the same file
` private function isCustomTable($field, $table){
if($this->endsWith($field, "_c")) {
$table = $table.'_cstm';
}
	return $table;
}

private function endsWith($haystack, $needle){
    $length = strlen($needle);
    if ($length == 0) {
        return true;
    }

    return (substr($haystack, -$length) === $needle);
}`
And wait for a proper fix

Worked fine in 7.11.1

Mac-Rae · 2020-09-04T14:17:43Z

This issue has been re-confirmed to exist and is replicable in the latest 7.10.x version.

While filtering for a custom field (Only via API is this issue observed) a "Database failure. Please refer to suitecrm.log for details." error

Thanks 👍

sutidor · 2020-09-30T11:49:15Z

Issue on 7.11.13, too. Fix works

zpangrsic · 2020-12-04T11:03:09Z

Hi. I Found a solution that is in a way "propper" and involves fixing the API V8/JsonApi/Repository/Filter.php which itterrates over the filters passed in the body of the Request. Inside it only needs to check the fields_defs definition for each field to see if it has 'source' set to 'custom_fields' which indicates the field is of type custom and exists inside the modules '_cstm' table.

Update this method on line 37 inside the file V8/JsonApi/Repository/Filter.php.

public function parseWhere(\SugarBean $bean, array $params)
{
        $operator = self::OP_AND;
        if (isset($params['operator'])) {
            $this->checkOperator($params['operator']);
            $operator = strtoupper($params['operator']);
            unset($params['operator']);
        }

        $params = $this->addDeletedParameter($params);

        $where = [];
        foreach ($params as $field => $expr) {
            $field_def = $bean->field_defs[$field];
            $is_custom = $field_def['source'] == 'custom_fields';
            if (empty($field_def)) {
                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',
                    ((!$is_custom)? $bean->getTableName(): $bean->get_custom_table_name()),
                    $field,
                    constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
                    $this->db->quoted($value)
                );
            }
        }

        return implode(sprintf(' %s ', $operator), $where);
}

Hope this helps.

JhoanMEF · 2021-01-13T15:39:40Z

Hola. Encontré una solución que es de alguna manera "adecuada" e implica arreglar la API V8 / JsonApi / Repository / Filter.php que se conecta a los filtros pasados en el cuerpo de la solicitud. En su interior, solo necesita verificar la definición de fields_defs para cada campo para ver si tiene 'source' establecido en 'custom_fields', lo que indica que el campo es de tipo personalizado y existe dentro de la tabla de módulos '_cstm'.

Actualice este método en la línea 37 dentro del archivo V8 / JsonApi / Repository / Filter.php.

public function parseWhere(\SugarBean $bean, array $params)
{
        $operator = self::OP_AND;
        if (isset($params['operator'])) {
            $this->checkOperator($params['operator']);
            $operator = strtoupper($params['operator']);
            unset($params['operator']);
        }

        $params = $this->addDeletedParameter($params);

        $where = [];
        foreach ($params as $field => $expr) {
            $field_def = $bean->field_defs[$field];
            $is_custom = $field_def['source'] == 'custom_fields';
            if (empty($field_def)) {
                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',
                    ((!$is_custom)? $bean->getTableName(): $bean->get_custom_table_name()),
                    $field,
                    constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
                    $this->db->quoted($value)
                );
            }
        }

        return implode(sprintf(' %s ', $operator), $where);
}

Espero que esto ayude.

This fixes the filter by api. But in suitecrm.log I see that the same error keeps getting

Wed Jan 13 15:33:12 2021 [25916][1][FATAL] Mysqli_query failed.
Wed Jan 13 15:33:12 2021 [25916][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM rest_clientes WHERE rest_clientes_cstm.numero_documento_c = '233333' AND rest_clientes.deleted = '0': MySQL error 1054: Unknown column 'rest_clientes_cstm.numero_documento_c' in 'where clause'

How could I solve it?

sheathy · 2021-02-26T14:36:58Z

Hi. Problem still exists in 7.11.18, I've applied above fix and it works, not sure if there are any side effects.

…odules

kale1d0code · 2021-07-20T11:14:25Z

broken for me
I would recommend everyone uses the fix provided by zpangrsic

…odules

holdusback · 2022-01-25T14:00:18Z

Using CRM v7.12.1

Got this error on every api request I do :
Tue Jan 25 14:50:23 2022 [104151][1][FATAL] Mysqli_query failed.
Tue Jan 25 14:50:23 2022 [104151][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM accounts WHERE accounts_cstm.siren_c = 'XXXXXXXXX' AND accounts.deleted = '0': MySQL error 1054: Unknown column 'accounts_cstm.siren_c' in 'where clause'

Here is my url request for you to understand :
https://xxxxx.com/Api/V8/module/Accounts?filter[siren_c][eq]=XXXXXXXXX

So im looking if this Siren (French ID of a company) already exist in my CRM. The code is working well. But I have this error log on every API request ...

Ofc siren_c exist in my CRM, I've checked in my database.

SuiteBot · 2022-02-09T10:54:34Z

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

https://community.suitecrm.com/t/unable-to-filter-with-custom-fields/83969/4

holdusback · 2022-04-05T13:09:40Z

Using CRM v7.12.1

Got this error on every api request I do : Tue Jan 25 14:50:23 2022 [104151][1][FATAL] Mysqli_query failed. Tue Jan 25 14:50:23 2022 [104151][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM accounts WHERE accounts_cstm.siren_c = 'XXXXXXXXX' AND accounts.deleted = '0': MySQL error 1054: Unknown column 'accounts_cstm.siren_c' in 'where clause'

Here is my url request for you to understand : https://xxxxx.com/Api/V8/module/Accounts?filter[siren_c][eq]=XXXXXXXXX

So im looking if this Siren (French ID of a company) already exist in my CRM. The code is working well. But I have this error log on every API request ...

Ofc siren_c exist in my CRM, I've checked in my database.

Just as info, I still have those line on every API request with a filter on 7.12.5 ....
The filter work great, but the error are here in the log, idk why

vaudoin · 2023-06-07T18:20:12Z

About the "SELECT COUNT(*) AS cnt" error that keeps rising with custom fields in filter, it is related to countRecords function which do no verify if a module has custom fields.

The following should resolve the issue:
Update method countRecords on Api\V8\BeanDecorator\BeanManager.php line 246

public function countRecords($module, $where)
    {

        $tableName = $this->newBeanSafe($module)->getTableName();

        $hasCustomFields = str_contains($where, '_cstm');
        if ($hasCustomFields){
            $tableName .= ', ' . $this->newBeanSafe($module)->get_custom_table_name();
        }

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

        return (int)$rowCount;
    }

I guess we could probably found a better way to find out if a module has custom fields. So comments are welcome!

Edit: Actually another open issue is dealing with the subject:
#9493

cameronblaikie added the Status:Requires Updates Issues & PRs which requires input or update from the author label Jun 7, 2019

Dillon-Brown added Area: API Issues & PRs related to all things regarding the API and removed Status:Requires Updates Issues & PRs which requires input or update from the author labels Jun 13, 2019

Dillon-Brown added Type:Bug Bugs within the core SuiteCRM codebase Priority:Critical Issues & PRs that are critical; broken core functionality, fatal errors - there are no workarounds labels Jul 11, 2019

LeoZandvliet mentioned this issue Aug 5, 2019

Previous Sprint Planning (16) 📅 🗣 #7673

Closed

YonatanRosemarin added a commit to YonatanRosemarin/SuiteCRM that referenced this issue May 18, 2020

salesagility#7285: allow filter of custom fields on V8 API Get Modules

6141c13

YonatanRosemarin mentioned this issue May 18, 2020

#7285: allow filter of custom fields on V8 API Get Modules #8731

Merged

6 tasks

mattlorimer added the Status:Fix Proposed A issue that has a PR related to it that provides a possible resolution label Apr 2, 2021

mattlorimer pushed a commit to YonatanRosemarin/SuiteCRM that referenced this issue May 26, 2021

Fix salesagility#7285 - Allow filter of custom fields on V8 API Get M…

1f59f67

…odules

mattlorimer pushed a commit that referenced this issue May 26, 2021

Fix #7285 - Allow filter of custom fields on V8 API Get Modules

e1c73f8

Dillon-Brown closed this as completed in e4312c8 Jun 2, 2021

RussellJuma mentioned this issue Jul 15, 2021

Can this be used with custom modules in SuiteCRM? RussellJuma/PySuiteCRM#1

Closed

Dillon-Brown pushed a commit to Dillon-Brown/SuiteCRM that referenced this issue Aug 24, 2021

Fix salesagility#7285 - Allow filter of custom fields on V8 API Get M…

7a0f2cf

…odules

Dillon-Brown pushed a commit that referenced this issue Aug 26, 2021

Fix #7285 - Allow filter of custom fields on V8 API Get Modules

61f18eb

Dillon-Brown pushed a commit to Dillon-Brown/SuiteCRM that referenced this issue Aug 27, 2021

Fix salesagility#7285 - Allow filter of custom fields on V8 API Get M…

45a6527

…odules

Dillon-Brown pushed a commit to Dillon-Brown/SuiteCRM that referenced this issue Sep 29, 2021

Fix salesagility#7285 - Allow filter of custom fields on V8 API Get M…

c641c08

…odules

Dillon-Brown pushed a commit that referenced this issue Sep 29, 2021

Fix #7285 - Allow filter of custom fields on V8 API Get Modules

8009fec

serfreeman1337 mentioned this issue Mar 1, 2022

V8 API: unable to filter on custom fields #9493

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

7.11.4,Database failure when filter custom fields by V8 API #7285

7.11.4,Database failure when filter custom fields by V8 API #7285

cekowu commented May 15, 2019 •

edited

cekowu commented May 15, 2019

cameronblaikie commented Jun 7, 2019

nathanle commented Jun 9, 2019 •

edited

nathanle commented Jun 11, 2019 •

edited

nathanle commented Jun 11, 2019 •

edited

nathanle commented Jun 13, 2019

danvasem commented Jun 22, 2019

isleshocky77 commented Jul 10, 2019

Dillon-Brown commented Jul 11, 2019 •

edited

cduquev commented Oct 23, 2019

jan-klima commented Oct 27, 2019

theerapatkij commented Nov 8, 2019

ghost commented Nov 14, 2019

pgorod commented Nov 15, 2019

artjomsmorscakovs commented Dec 9, 2019

Lehnerr commented Jan 14, 2020

pgorod commented Jan 14, 2020

artjomsmorscakovs commented Jan 18, 2020

Lehnerr commented Jan 19, 2020

suneelIBMkummar commented Jan 30, 2020

pgorod commented Jan 30, 2020

rpinzon commented Apr 13, 2020

Mac-Rae commented Sep 4, 2020

sutidor commented Sep 30, 2020

zpangrsic commented Dec 4, 2020

JhoanMEF commented Jan 13, 2021 •

edited

sheathy commented Feb 26, 2021

kale1d0code commented Jul 20, 2021

holdusback commented Jan 25, 2022

SuiteBot commented Feb 9, 2022

holdusback commented Apr 5, 2022

vaudoin commented Jun 7, 2023 •

edited

7.11.4,Database failure when filter custom fields by V8 API #7285

7.11.4,Database failure when filter custom fields by V8 API #7285

Comments

cekowu commented May 15, 2019 • edited

Issue

Expected Behavior

Actual Behavior

Possible Fix

Steps to Reproduce

Context

Your Environment

cekowu commented May 15, 2019

cameronblaikie commented Jun 7, 2019

nathanle commented Jun 9, 2019 • edited

nathanle commented Jun 11, 2019 • edited

nathanle commented Jun 11, 2019 • edited

nathanle commented Jun 13, 2019

danvasem commented Jun 22, 2019

isleshocky77 commented Jul 10, 2019

Dillon-Brown commented Jul 11, 2019 • edited

cduquev commented Oct 23, 2019

jan-klima commented Oct 27, 2019

theerapatkij commented Nov 8, 2019

ghost commented Nov 14, 2019

pgorod commented Nov 15, 2019

artjomsmorscakovs commented Dec 9, 2019

Lehnerr commented Jan 14, 2020

pgorod commented Jan 14, 2020

artjomsmorscakovs commented Jan 18, 2020

Lehnerr commented Jan 19, 2020

suneelIBMkummar commented Jan 30, 2020

pgorod commented Jan 30, 2020

rpinzon commented Apr 13, 2020

Mac-Rae commented Sep 4, 2020

sutidor commented Sep 30, 2020

zpangrsic commented Dec 4, 2020

JhoanMEF commented Jan 13, 2021 • edited

sheathy commented Feb 26, 2021

kale1d0code commented Jul 20, 2021

holdusback commented Jan 25, 2022

SuiteBot commented Feb 9, 2022

holdusback commented Apr 5, 2022

vaudoin commented Jun 7, 2023 • edited

cekowu commented May 15, 2019 •

edited

nathanle commented Jun 9, 2019 •

edited

nathanle commented Jun 11, 2019 •

edited

nathanle commented Jun 11, 2019 •

edited

Dillon-Brown commented Jul 11, 2019 •

edited

JhoanMEF commented Jan 13, 2021 •

edited

vaudoin commented Jun 7, 2023 •

edited