cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Query filter in "GenericThing" not working

SOLVED
Visitor

Query filter in "GenericThing" not working

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

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Query filter in "GenericThing" not working

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.

9 REPLIES 9
Highlighted

Re: Query filter in "GenericThing" not working

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.

Re: Query filter in "GenericThing" not working

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.

Re: Query filter in "GenericThing" not working

Hi Pavan Kumar Kodur​,

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

Re: Query filter in "GenericThing" not working

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.

Re: Query filter in "GenericThing" not working

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!

Re: Query filter in "GenericThing" not working

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?

Re: Query filter in "GenericThing" not working

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

Re: Query filter in "GenericThing" not working

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.

Re: Query filter in "GenericThing" not working

Thanks Ankit Gupta, that did the trick.