REST API filter for list of IDs

liero · 12 September 2021 12:38

( SuiteCRM Version 7.11.20 JSON API v8)
Hi
I’m using the suitecrm Json REST API to access data and i’m trying to compose a query that results with all records that fit to a list of IDs.
According to the documentation here: JSON API :: SuiteCRM Documentation
I’ve composed the below request:
GET https://domainname.com/Api/V8/module/tablename?filter[operator]=or&filter[id][eq]=c46ff350-c3e6-d263-ca3e-613dd3f6cddc&filter[id][eq]=7fdfd47f-f2a7-442b-411b-613dd45edbe8
This returns all the records in the table.

Tried changing the filter operation to ‘and’ : returned only the record of the last id: 7fdfd47f-f2a7-442b-411b-613dd45edbe8 < this seems like a reasonable behavior.

So, is this doable without me quering for every record separately ?

Also tried this kind of API that didn’t work:
https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.0/Integration/Web_Services/REST_API/Endpoints/modulefilter_GET/
not sure if its supported in suiteCRM though

Thanks in advance!
Y

John · 15 October 2021 14:15

Hey there,

Have you found a solution for this?

This issue rings a bell, I believe I’ve found issues previously with the API’s OR operator

I’ve had a look around and found this old thread:

It seems that this is a known issue with the API, raised as a Bug here:

github.com/salesagility/SuiteCRM

V8 API Filtering W/ OR Operator Chained Conditions

opened 03:43PM - 06 Dec 19 UTC

Jason-Dang

Type:Bug Priority:Important Area: API

#### Issue When filtering on an API GET modules endpoint, using the `AND` ope…rator to chain conditions appears to work fine with the below basic example. However, using the `OR` operator results in no filters being applied from what I can tell as all records are returned regardless of whether they match the criteria. Working `AND` example: ``` /V8/module/Accounts?filter[name][eq]=Test&filter[operator]=and&filter[account_type][eq]=Customer ``` Broken `OR` example: ``` /V8/module/Accounts?filter[name][eq]=Test&filter[operator]=or&filter[account_type][eq]=Customer ``` #### Expected Behavior Only records matching the filter criteria should be returned and not the full list. #### Actual Behavior All filters are ignored and the full list is returned. #### Possible Fix Debug endpoint, identify where the filters are either overwritten or dropped and implement a fix. #### Steps to Reproduce 1. Create an Account record that matches the filter criteria outlined above. 2. Create a one or more Account records that does not match the criteria. 3. Apply the above outlined filters to the GET modules API call, targeting Accounts. 4. Run some more tests with varying modules and fields filtered etc. #### Context High Priority - Unable to filter with multiple conditions using `OR` operator. This means the only way to achieve the same result is by making multiple calls (one for each option), which obviously increases overhead. #### Your Environment * SuiteCRM Version used: 7.10.18 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Postman v6 * Environment name and version (e.g. MySQL, PHP 7): PHP 7.1, 7.3 * Operating System and version (e.g Ubuntu 16.04): Ubuntu 16.04

With a proposed solution here:

github.com/salesagility/SuiteCRM

Fixed #8366 - V8 API Filtering W/ OR Operator Chained Conditions

salesagility:hotfix-7.10.x ← serfreeman1337:bugfix_8366

opened 09:02PM - 27 Jan 20 UTC

serfreeman1337

+23 -3

# Description Issue reference: #8366 Grouped out filter params for query. … ## Motivation and Context Without grouping we fail to apply OR filter. Example of generated query: ```sql contacts.phone_mobile = '...' OR contacts.phone_work = '...' OR contacts.deleted = '0' ``` Should be: ```sql (contacts.phone_mobile = '...' OR contacts.phone_work = '...') AND contacts.deleted = '0' ``` ## How To Test This * Create a Contact and set Phone Work and Phone Mobile for some values. * Apply OR filter to GET modules API call, targeting Contacts. ``` /V8/module/Contacts?filter[operator]=or&filter[phone_mobile][eq]=some_value&filter[phone_work][eq]=some_value ``` * Run some more tests with varying filter value. ## Types of changes - [x] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [ ] Breaking change (fix or feature that would cause existing functionality to change) ### Final checklist - [x] My code follows the code style of this project found [here](https://docs.suitecrm.com/community/contributing-code/coding-standards/). - [ ] My change requires a change to the documentation. - [x] I have read the [**How to Contribute**](https://docs.suitecrm.com/community/contributing-code/) guidelines.

Note that this change would be non-upgrade safe, as it is not merged into the main product

Does the above change help in resolving your API issues?

liero · 16 October 2021 05:38

Thanks for your reply John!

I’ve checked out the code that @serfreeman1337 wrote, only this commit:

Tried running the below query returned only single record (valid for the last id) in the result set:
Api/V8/module/Contacts?filter[operator]=or&filter[id][eq]=7db9eae4-a8c1-b73c-45ad-6158a2dd3d95&filter[id][eq]=cd833f5c-5837-0244-1484-6158a60df4c4

The above two ids are valid records so with this query I expected to get both.

Note i’m not a php dev (only java / c# dev).
I’ve manually merged the changes to Filter.php to overwrite my version of the file.
restarted the webserver (apache)
Than I ran Admin portal > Repair > Quick Repair and Rebuild
Hope its the right way to rebuild…

Again, Thanks

serfreeman1337 · 16 October 2021 07:51

This quick fix should allow array values for requests:

gist.github.com

https://gist.github.com/serfreeman1337/4edcc05da6a9ccfd26047421ffa88449

Filter.php

<?php
namespace Api\V8\JsonApi\Repository;

class Filter
{
    // operators so far
    const OP_EQ = '=';
    const OP_NEQ = '<>';
    const OP_GT = '>';
    const OP_GTE = '>=';

This file has been truncated. show original

example request:
Api/V8/module/Contacts?filter[operator]=or&filter[id][eq][]=7db9eae4-a8c1-b73c-45ad-6158a2dd3d95&filter[id][eq][]=cd833f5c-5837-0244-1484-6158a60df4c4

note the []