Suitecrm: 7.11.4,Database failure when filter custom fields by V8 API

Created on 15 May 2019  ·  27Comments  ·  Source: salesagility/SuiteCRM



Issue

Expected Behavior

Actual Behavior


Possible Fix

Steps to Reproduce


  1. Upgrade to 7.11.4 version
  2. Filter custom fields by V8 API , etg:
    SuiteCRM/Api/V8/module/Accounts?filter[operator]=and&filter[xx_c][eq]=431565
  3. Response:
    { "errors": { "status": 400, "title": null, "detail": "Database failure. Please refer to suitecrm.log for details." } }
  4. 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):
API Critical Fix Proposed Bug
Source
picture cekowu
👍3

Most helpful comment

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

picture artjomsmorscakovs on 9 Dec 2019
👍4 ❤3 🎉1

All 27 comments

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

cekowu picture cekowu on 15 May 2019

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,

cameronblaikie picture cameronblaikie on 7 Jun 2019

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'

mysql> SHOW columns FROM accounts;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| id | char(36) | NO | PRI | NULL | |
| name | varchar(150) | YES | MUL | NULL | |
| date_entered | datetime | YES | | NULL | |
| date_modified | datetime | YES | | NULL | |
| modified_user_id | char(36) | YES | | NULL | |
| created_by | char(36) | YES | | NULL | |
| description | text | YES | | NULL | |
| deleted | tinyint(1) | YES | MUL | 0 | |
| assigned_user_id | char(36) | YES | | NULL | |
| account_type | varchar(50) | YES | | NULL | |
| industry | varchar(100) | YES | | NULL | |
| annual_revenue | varchar(100) | YES | | NULL | |
| phone_fax | varchar(100) | YES | | NULL | |
| billing_address_street | varchar(150) | YES | | NULL | |
| billing_address_city | varchar(100) | YES | | NULL | |
| billing_address_state | varchar(100) | YES | | NULL | |
| billing_address_postalcode | varchar(20) | YES | | NULL | |
| billing_address_country | varchar(255) | YES | | NULL | |
| rating | varchar(100) | YES | | NULL | |
| phone_office | varchar(100) | YES | | NULL | |
| phone_alternate | varchar(100) | YES | | NULL | |
| website | varchar(255) | YES | | NULL | |
| ownership | varchar(100) | YES | | NULL | |
| employees | varchar(10) | YES | | NULL | |
| ticker_symbol | varchar(10) | YES | | NULL | |
| shipping_address_street | varchar(150) | YES | | NULL | |
| shipping_address_city | varchar(100) | YES | | NULL | |
| shipping_address_state | varchar(100) | YES | | NULL | |
| shipping_address_postalcode | varchar(20) | YES | | NULL | |
| shipping_address_country | varchar(255) | YES | | NULL | |
| parent_id | char(36) | YES | MUL | NULL | |
| sic_code | varchar(10) | YES | | NULL | |
| campaign_id | char(36) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
33 rows in set (0.00 sec)

mysql> SHOW columns FROM accounts_cstm;
+--------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------+--------------+------+-----+---------+-------+
| id_c | char(36) | NO | PRI | NULL | |
| salesforceid_c | varchar(255) | YES | | NULL | |
| salesforceparentid_c | varchar(255) | YES | | NULL | |
| account_id_c | char(36) | YES | | NULL | |
| sub_market__c | text | YES | | NULL | |
| isexcludedfromrealign_c | varchar(1) | YES | | NULL | |
| lastactivitydate_c | datetime | YES | | NULL | |
| systemmodstamp_c | datetime | YES | | NULL | |
| lastmodifiedbyid_c | varchar(255) | YES | | NULL | |
| lastmodifieddate_c | datetime | YES | | NULL | |
| createdbyid_c | text | YES | | NULL | |
| createddate_c | datetime | YES | | NULL | |
| ownerid_c | varchar(255) | YES | | NULL | |
| isdeleted_c | varchar(1) | YES | | NULL | |
| parentid_c | varchar(255) | YES | | NULL | |
| recordtypeid_c | varchar(255) | YES | | NULL | |
| billingstreet_c | varchar(255) | YES | | NULL | |
| billingcity_c | varchar(255) | YES | | NULL | |
| billingpostalcode_c | varchar(255) | YES | | NULL | |
| phone_c | varchar(255) | YES | | NULL | |
| billingstate_c | varchar(255) | YES | | NULL | |
| industry_c | varchar(100) | YES | | NULL | |
| accountsource_c | varchar(255) | YES | | NULL | |
| createuserid__c | varchar(255) | YES | | NULL | |
| mclabs2__osf_id__c | varchar(255) | YES | | NULL | |
| address2__c_city_c | varchar(100) | YES | | NULL | |
| address2__c_state_c | varchar(100) | YES | | NULL | |
| address2__c_postalcode_c | varchar(20) | YES | | NULL | |
| address2__c_country_c | varchar(100) | YES | | NULL | |
| address2__c | varchar(255) | YES | | NULL | |
| altphone__c | varchar(255) | YES | | NULL | |
| group__c | varchar(255) | YES | | NULL | |
| extention__c | varchar(255) | YES | | NULL | |
| category__c | varchar(255) | YES | | NULL | |
| cell_mobile__c | varchar(255) | YES | | NULL | |
| mclabs2__count_of_properties_c | int(255) | YES | | NULL | |
| shippingstreet_c | varchar(255) | YES | | NULL | |
| shippingcity_c | varchar(255) | YES | | NULL | |
| shippingpostalcode_c | varchar(255) | YES | | NULL | |
| fax_c | varchar(255) | YES | | NULL | |
| type_c | varchar(255) | YES | | NULL | |
| billingcountry_c | varchar(255) | YES | | NULL | |
+--------------------------------+--------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

nathanle picture nathanle on 9 Jun 2019

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 picture nathanle on 11 Jun 2019

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 picture nathanle on 11 Jun 2019

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.

nathanle picture nathanle on 13 Jun 2019

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?

danvasem picture danvasem on 22 Jun 2019

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?

isleshocky77 picture isleshocky77 on 10 Jul 2019

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

Dillon-Brown picture Dillon-Brown on 11 Jul 2019

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

cduquev picture cduquev on 23 Oct 2019
👍2

@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.
jan-waterdrop picture jan-waterdrop on 27 Oct 2019

@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.

theerapatkij picture theerapatkij on 8 Nov 2019

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

ahmed-cader picture ahmed-cader on 14 Nov 2019

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

pgorod picture pgorod on 15 Nov 2019

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

artjomsmorscakovs picture artjomsmorscakovs on 9 Dec 2019
👍4 ❤3 🎉1

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

Lehnerr picture Lehnerr on 14 Jan 2020

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...

pgorod picture pgorod on 14 Jan 2020

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

artjomsmorscakovs picture artjomsmorscakovs on 18 Jan 2020
👍2

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

Lehnerr picture Lehnerr on 19 Jan 2020
👍1

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!!!!

suneelIBMkummar picture suneelIBMkummar on 30 Jan 2020

Can anyone mitigate the impact the @Lehnerr mentioned above?

pgorod picture pgorod on 30 Jan 2020

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

rpinzon picture rpinzon on 13 Apr 2020

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 :+1:

Mac-Rae picture Mac-Rae on 4 Sep 2020

Issue on 7.11.13, too. Fix works

sutidor picture sutidor on 30 Sep 2020

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.

zpangrsic picture zpangrsic on 4 Dec 2020
👍4

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?

JhoanMEF picture JhoanMEF on 13 Jan 2021

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

sheathy picture sheathy on 26 Feb 2021
Was this page helpful?
0 / 5 - 0 ratings

Related issues

connorshea picture connorshea  ·  3Comments
sasha2002 picture sasha2002  ·  3Comments
pgorod picture pgorod  ·  3Comments
Mausino picture Mausino  ·  3Comments
dawansv picture dawansv  ·  3Comments