Filter api calls by non db feilds

šŸ‘Øā€šŸ’» Development Help
,
1

im trying to find a way to filter the api by a non db field, but without making cutom api endpoint, is there something that can help me do that ?

Version: SuiteCRM 8.10.2 (legacy layer at public/legacy). The calls go through the V8 REST API (/legacy/Api/V8/...).

The field: itā€™s a custom non-db field I created on Tasks, not a relate field:

// custom/Extension/modules/Tasks/Ext/Vardefs/lead_phone_display.php
$dictionary['Task']['fields']['lead_phone_display'] = [
    'name' => 'lead_phone_display',
    'vname' => 'LBL_LEAD_PHONE_DISPLAY',
    'type' => 'phone',
    'source' => 'non-db',
];

It shows the phone of the Lead the Task is related to (parent_type = 'Leads', parent_id = the Leadā€™s id). An after_retrieve logic hook fills it from leads.phone_mobile (or phone_work), so the value appears correctly in the API response.

The problem: I want to filter Tasks by that phone through the API, e.g. all Tasks whose Leadā€™s phone starts with 0112:

GET /legacy/Api/V8/module/Tasks?filter[lead_phone_display][like]=0112%

This returns 400 ā€œDatabase failureā€, and suitecrm.log shows:

Query Failed: SELECT COUNT(*) AS cnt FROM tasks LEFT JOIN tasks_cstm ON (tasks.id = tasks_cstm.id_c)
WHERE (tasks.lead_phone_display LIKE '0112%') AND tasks.deleted = '0':
MySQL error 1054: Unknown column 'tasks.lead_phone_display' in 'WHERE'

As far as I can tell, Api/V8/JsonApi/Repository/Filter.php always builds <table>.<field>, so a non-db field canā€™t be filtered. Logic hooks run after the query, so they canā€™t help.

What Iā€™ve got working: I override GetModulesParams in custom/application/Ext/Api/V8/params.php with a custom filter option. It removes lead_phone_display from the filter and adds a subquery instead:

AND tasks.id IN (
  SELECT t.id FROM tasks t
    JOIN leads l ON l.id = t.parent_id AND l.deleted = 0
   WHERE t.parent_type = 'Leads'
     AND (l.phone_mobile LIKE '0112%' OR l.phone_work LIKE '0112%'))

That works, but it feels like a workaround.

My question: is there a supported way in 8.10 to make the V8 filter work on a non-db field like this? For example, a vardef setting, or an extension point Iā€™ve missed. Or would you recommend storing the phone in a real column (a custom field kept in sync with hooks) instead?

Thanks!

2

Welcome to the community!

Could you please specify which version of SuiteCRM you are using? Also, could you explain your question in a little more detail, preferably with an example? For instance, are you referring to a specific non-db field type(relate type) or a custom non-db field that you have created?

1 Like
3

Hi, thanks for replying!

Version: SuiteCRM 8.10.2 (legacy layer at public/legacy). The calls go through the V8 REST API (/legacy/Api/V8/...).

The field: itā€™s a custom non-db field I created on Tasks, not a relate field:

// custom/Extension/modules/Tasks/Ext/Vardefs/lead_phone_display.php
$dictionary['Task']['fields']['lead_phone_display'] = [
    'name' => 'lead_phone_display',
    'vname' => 'LBL_LEAD_PHONE_DISPLAY',
    'type' => 'phone',
    'source' => 'non-db',
];

It shows the phone of the Lead the Task is related to (parent_type = 'Leads', parent_id = the Leadā€™s id). An after_retrieve logic hook fills it from leads.phone_mobile (or phone_work), so the value appears correctly in the API response.

The problem: I want to filter Tasks by that phone through the API, e.g. all Tasks whose Leadā€™s phone starts with 0112:

GET /legacy/Api/V8/module/Tasks?filter[lead_phone_display][like]=0112%

This returns 400 ā€œDatabase failureā€, and suitecrm.log shows:

Query Failed: SELECT COUNT(*) AS cnt FROM tasks LEFT JOIN tasks_cstm ON (tasks.id = tasks_cstm.id_c)
WHERE (tasks.lead_phone_display LIKE '0112%') AND tasks.deleted = '0':
MySQL error 1054: Unknown column 'tasks.lead_phone_display' in 'WHERE'

As far as I can tell, Api/V8/JsonApi/Repository/Filter.php always builds <table>.<field>, so a non-db field canā€™t be filtered. Logic hooks run after the query, so they canā€™t help.

My question: is there a supported way in 8.10 to make the V8 filter work on a non-db field like this? For example, a vardef setting, or an extension point Iā€™ve missed. Or would you recommend storing the phone in a real column (a custom field kept in sync with hooks) instead?

Thanks!

4

I havenā€™t tried but may be you can do something like in this file Api/V8/JsonApi/Repository/Filter.php

You can try something like this.

PS: I didnā€™t tried exactly, but you can play around.

if ($bean->module_name === ā€˜Tasksā€™ && $field === ā€˜lead_phone_displayā€™) {

foreach ($expr as $op => $value) {

    $this->checkOperator($op);

    $sqlOp = constant(sprintf('%s::OP\_%s', self::class, strtoupper($op)));

    $quoted = $this->db->quoted($value);

    $where\[\] = sprintf(

        "tasks.parent_type = 'Leads' AND tasks.parent_id IN (

            SELECT leads.id FROM leads

            WHERE leads.deleted = 0

            AND (leads.phone_mobile %s %s)

        )",

        $sqlOp, $quoted,

        $sqlOp, $quoted,

        $sqlOp, $quoted

    );

}

continue;

}