9 Replies Latest reply on May 18, 2017 9:21 AM by pkodur RSS
    pkodur Newbie

    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

      • Re: Query filter in "GenericThing" not working
        Ankit Gupta Ninja

        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
            pkodur Newbie

            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
                Ankit Gupta Ninja

                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
                  Ankit Gupta Ninja

                  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
                      pkodur Newbie

                      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
                          Ankit Gupta Ninja

                          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
                              pkodur Newbie

                              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