In Operator for V8 API

💡 Suggestion Box
1

I’ve been writing a graphQL like API client for the SuiteCRM V8 API
which turns a graph object into a nested JSON of records.
example of such a graph object I pulled from one of my projects

const requestParameterBuilder: (state: RootState) => ResolveRecordGraphParameters = (state) => {
  const now = selectNow(state);
  return ({
    type: CRM_RECORD_TYPE__SESSION,
    filtering: [
      {
        field: CRM_RECORD_FIELD_NAME__SESSION__DATE_START,
        operator: 'GTE',
        value: getWeekBoundaryISODateString(
          "start",
          getDateShiftedByWeeks(1, now, "past")
        )
      }, {
        field: CRM_RECORD_FIELD_NAME__SESSION__DATE_START,
        operator: 'LTE',
        value: getWeekBoundaryISODateString(
          "end",
          getDateShiftedByWeeks(1, now, "future")
        )
      }
    ],
    fields: [
      CRM_RECORD_FIELD_NAME__COMMON__NAME,
      CRM_RECORD_FIELD_NAME__COMMON__ASSIGNED_USER_ID,
      CRM_RECORD_FIELD_NAME__SESSION__DATE_START,
      CRM_RECORD_FIELD_NAME__SESSION__DATE_END,
    ],
    links: [
      {
        name: CRM_RELATIONSHIP_NAME__SESSIONS__TO__PARTICIPANTS,
        fields: personsNameCRMFields,
      },
      {
        name: CRM_RELATIONSHIP_NAME__SESSIONS__TO__SESSION_ATTENDANCE_TRACKERS,
        fields: [
          CRM_RECORD_FIELD_NAME__SESSION_ATTENDANCE_TRACKER__OUTCOME,
          CRM_RECORD_FIELD_NAME__COMMON__DESCRIPTION,
          CRM_FORIGN_KEY_NAME__FROM__SESSION_ATTENDANCE_TRACKERS__SESSION_ATTENDANCE_TRACKERS__TO__PARTICIPANTS,
          CRM_FORIGN_KEY_NAME__FROM__SESSION_ATTENDANCE_TRACKERS__SESSIONS__TO__SESSION_ATTENDANCE_TRACKERS
        ],
        links: [
          {
            name: CRM_RELATIONSHIP_NAME__SESSION_ATTENDANCE_TRACKERS__TO__ATTENDANCE_TRACKER_EVENTS,
            fields: [
              CRM_RECORD_FIELD_NAME__ATTENDANCE_TRACKER_EVENT__DATETIME,
              CRM_FORIGN_KEY_NAME__FROM__ATTENDANCE_TRACKER_EVENTS__SESSION_ATTENDANCE_TRACKERS__TO__ATTENDANCE_TRACKER_EVENTS
            ]
          }
        ]
      }
    ]
  });
};

you can imagine what the returned json looks like

CRMRecord
{
  id: string;
  type; string;
  attributes: {
    [key:string]: string;
  }
  related: {
    [key: string]: CRMRecord[];
  }
}

The client does this by walking across the graph and making the corresponding API calls as it goes. Starting with an initial call to:
GET {{suitecrm.url}}/Api/V8/module/{moduleName}
or:
GET {{suitecrm.url}}/Api/V8/module/{moduleName}/{id}
then calling:
GET {{suitecrm.url}}/Api/V8/module/{moduleName}/{id}/relationships/{linkFieldName}
switching back and forth until the graph is complete.

The issue I am facing is with the relationship calls,
If the returned related records also contained their relationship information, it wouldn’t be an issue (I guessed this was done to speed up the API) but this means this api graph client needs to make an additional request for records to get their relationship information.

to explain better currently if the graph is to resolve 3 nested relationships and starting the graph with one record.
to resolve the third level of nested records the API would need to be called independently for each prior record in the graph.

This problem gets exponentially worse with more complicated graphs.

There are two ways I propose to solve this,
1: return relationship information on records returned by GET {{suitecrm.url}}/Api/V8/module/{moduleName}/{id}/relationships/{linkFieldName}
2: my proposal
I have already tried calling the API once using the OR operator and EQ operators on the id field. Unfortunately the SQL which is generated from this causes only one record to be returned.
example query string from these API calls

fields[ENG_Students]=first_name,last_name&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[id][EQ]=<REDACTED>&filter[operator]=OR

I would like to call the API with a filter operator something like the following.

fields[ENG_Students]=first_name,last_name&filter[id][IN]=%22<REDACTED>%22%2C%22<REDACTED>%22%2C%22<REDACTED>%22%2C%22<REDACTED>%22%2C%22<REDACTED>%22%2C%22<REDACTED>%22%2C%22<REDACTED>%22%2C%22<REDACTED>%22

maybe picking different symbol for delimiter but you get the idea.

This will generate SQL with WHERE clause like module.id IN ('<REDACTED>',<REDACTED>','<REDACTED>'...)

The above change will significantly improve the performance of resolving graphs with my API client and on top of this I believe adding an IN operator will also allow for more use cases for the API in general. (mixing AND and OR like filter behaviour is one I can think of on top of my head)

2

I’ve added IN and NOT IN operators to my dev CRM and this seems to have solved my performance issue without altering the code base too much.

the only change that had to be made is that the quotemarks in the query need to be url decoded (this only happens for IN and NOT IN operations)

I’ll made a PR for this here which is pretty much what I have running now in my environment

2 Likes
3

I have since updated my graph resolving client to no longer rely on {{suitecrm.url}}/Api/V8/module/{moduleName}/{id} so much
its kind of a redundant entrypoint if not for the filtering capabilities.

I think it would make sense to allow users to filter and specify fields on {{suitecrm.url}}/Api/V8/module/{moduleName}/{id}/relationships/{linkFieldName} but this is how the CRM works currently.

Regardless, I still feel as if IN and NOT IN are relevant additions to the V8 API Filtering capabilities