Query filter in "GenericThing" not working

[pkodur]

Folks,

I'm trying to invoke a service and pass filter parameter in "GenericThing" template. The service returns all data ignoring the filter.  I need the service to return data based on filter parameter. Any help is appreciated. Here's my code for reference:

var filter = '{"filters": {"fieldName":"user_id","type":"EQ","value": "T1234"}}';

var params = {
    resourceProvider: "AdminUserProfileResourceProvider",
    serviceName: "GetUserProfiles",
    params: {
        dataShape: {
            fieldDefinitions: {              
                query: {
                    name: "query",
                    baseType: "QUERY"
                }
            }
        },
        rows: [{
            query: filter
        }]
    }
}

// result: INFOTABLE
var result = Things["PTC.Resource.ResourceManager"].CallServiceOnProvider(params);

Thanks!

Pavan

[ankigupta]

Hi Pavan Kumar Kodur​,

Per my understanding the single quote should not used in the query:

Try this

var filter = {"filters": {"fieldName":"user_id","type":"EQ","value": "T1234"}};

I hope it helps.

[pkodur]

Ankit Gupta,

Thank you for your response.

I tried changing the filter as per the suggestion. I still fetch all records. The issue still remains as "Filter" is not set.

[ankigupta]

Hi Pavan Kumar Kodur​,

The filter seems correct. Could you please CallServiceOnProvider Service; how is it applying the filter?

[ankigupta]

Hi Pavan Kumar Kodur​,

Also try following:

rename your result infotable to result1 and add following code:

var params = {
    t: result1 /* INFOTABLE */,
    query: filter /* QUERY */
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].Query(params);

I hope it helps.

[pkodur]

Ankit Gupta,

As my target table holds millions of records, building a InfoTable with huge volume is totally out of reach. I need to pass the filter to the query and fetch only finite data as needed. I appreciate your suggestion but unfortunately it's not the best fit for my use case. Thanks!

[ankigupta]

Pavan Kumar Kodur​,

Where is the data stored? And please share the complete flow of the service. CallServiceOnProvider is a custom service used in the service shared by you. What does this service do?

[pkodur]

Here's the total flow:

Thing: Connector [Name:OracleConnector]
ORACLE connector is used to fetch data from legacy system

ThingTemplate: [Name: OracleConnectorTemplate]
BaseThingTemplate: ojdbc6JDBCTemplate
Service: GetUserProfiles
Parameter:
Input: UserId [STRING]
OutPut: Result [INFOTABLE]
SQL Query:
Select user_id, first_name, last_name from user_profile
where ([[UserId]] is null or user_id = ([[UserId]]))
Order by user_id

Thing: [Name: AdminUserProfileResourceProvider]
ThingTemplate: OracleConnectorTemplate
Service: GetUserProfiles
Parameter:
Input: UserId [STRING]
OutPut: Result [INFOTABLE]

Thing: [Name: MblResourceServiceProvider]
ThingTemplate: GenericThing
Service: GetUserProfile
Parameter:
Input: UserId [STRING]
OutPut: Result [INFOTABLE]
Local(JavaScript): [I also tried the below approach along with my initial approach of using "CallServiceOnProvider"]
var query =  {
    "filters":  {
        "fieldName": "user_id",
        "type": "EQ",
        "value": "A1234"
    }
};

var params =  {
    query: query,
    extraParams: undefined,
    dataShapeName: undefined,
    dataShape: undefined
};

var result = Things["AdminUserProfileResourceProvider"].GetUserProfiles(params);

When I test my service, I get back all records without applying UserId filter.

Note: When I test OracleConnectorTemplate & AdminUserProfileResourceProvider with UserId input param it works. I get back the result for the user id passed.

I'm open to have a WebEx meeting if required. Thanks for your assistance.

--Pavan

[ankigupta · Accepted solution]

Hi Pavan Kumar Kodur​,

Could you please explain the requirement to apply the filter in JavaScript when you have already applied the filter in the Sql Query?

Just try to run following service in your generic Thing and you will get the desired result.

var params = {
    UserId: "A1234" /* STRING */
};
// result: INFOTABLE
var result = Things["AdminUserProfileResourceProvider"].GetUserProfiles(params);

I hope it helps.

[pkodur]

Thanks Ankit Gupta, that did the trick.