Fuzzy Search in OData

[sreenu0248]

We all know about the wildcard search, as a wildcard character in a Select query is used to locate a specific item when you can’t remember exactly what to search. One of the drawbacks of wildcard search is, it will fetch the exactly matched records only. ( For example, if we search the Airline Code code with A* we will get the results with A only it won't get the data with a. )

We will overcome this drawback by using the fuzzy search functionality where we can fetch the data based on the fuzzy patterns. An additional advantage of fuzzy search is we can do the filter/search operations on the two columns in a single call/search.

Please follow the below steps to implement the OData service with fuzzy search

CDS View

@AbapCatalog.sqlViewName: 'Zfuzzy_search'

@AbapCatalog.compiler.CompareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Fuzzy Search'

define view ZFuzy_Srch as select from scarr {

 carrid,   

 carrname,

 currcode,

 url

}

 ABAP Managed Database Procedure (AMDP)

CLASS zcl_fuzy_srch DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

   interfaces IF_AMDP_MARKER_HDB.

*** --- Custom Structure

   TYPES: BEGIN OF ty_airlines,

          CARRID   TYPE S_CARR_ID,

          CARRNAME TYPE S_CARRNAME,

          CURRCODE TYPE S_CURRCODE,

          URL      TYPE S_CARRURL,

   end of TY_AIRLINES.

*** --- Table Type

types: tt_airlines TYPE TABLE OF ty_airlines .

class-methods Airlines_GLBSRCH

importing

        value(im_CARRNAME) type S_CARRNAME

exporting

        value(Ex_GLBSRCH) type tt_airlines.

ENDCLASS.







CLASS zcl_fuzy_srch IMPLEMENTATION.

METHOD Airlines_GLBSRCH BY DATABASE PROCEDURE FOR HDB

LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING Zfuzzy_search.



Ex_GLBSRCH = select CARRID,CARRNAME,CURRCODE,URL

from Zfuzzy_search

where



(



contains( CARRID,       :im_CARRNAME, fuzzy( 1.0) ) or



contains( CARRNAME,    :im_CARRNAME, fuzzy( 1.0) )





)



;

endmethod.

ENDCLASS.

Here fuzzy( 1.0 ) represents Fuzzy patterns. We will have a fuzzy pattern range from 0.1 to 1.0 which define the accuracy rate of search.

OData Service:

Create a new Project in the SEGW Tcode.



Now import the properties of  Zfuzzy_search view.



Select the required fields/properties and mandatory to select at least one key filed from the list.

Now we need to map our CDS Database/SQL view(Zfuzzy_search) and CDS entity(ZFuzy_Srch). on click on the map to data source popup populates to select CDS business entity:

 



Provide the CDS entity name ZFuzy_Srch and do the mapping for the corresponding fields/properties.



After mapping is done save the changes and click on generate icon as shown below to generate run time artifacts.

Now, redefine the FUZZYSET_GET_ENTITYSET method.

 

  METHOD fuzzyset_get_entityset.

*** --- Data Declarations

    DATA:lv_carrname TYPE  s_carrname.

*** --- Class Instantion

    DATA(obj) = NEW zcl_fuzy_srch( ).

*** --- Get Filters

    LOOP AT it_filter_select_options INTO DATA(ls_filters).

      CASE ls_filters-property.

        WHEN 'Carrname'.

          IF ls_filters-select_options[ 1 ]-low IS NOT INITIAL.

            lv_carrname       = ls_filters-select_options[ 1 ]-low.

          ENDIF.

      ENDCASE.

    ENDLOOP.

*** --- Get The Airline details

    obj->airlines_glbsrch(

      EXPORTING

        im_carrname = lv_carrname

      IMPORTING

        ex_glbsrch  = DATA(ls_data)

    ).

    IF ls_data IS NOT INITIAL.

      et_entityset = CORRESPONDING #( ls_data ).

    ENDIF.

    IF et_entityset IS NOT INITIAL.

** -- Inline Count

      es_response_context-inlinecount = lines( et_entityset ).

** -- Paging

      CALL METHOD /iwbep/cl_mgw_data_util=>paging

        EXPORTING

          is_paging = is_paging

        CHANGING

          ct_data   = et_entityset.

    ENDIF.

  ENDMETHOD.

Register the service.

Step4:

URI: sap/opu/odata/sap/Zfuzy_Srch_SRV/FuzzySet?$filter=Carrname eq 'a*'



While passing the input we can give either in upper/lower case it will fetch the desired results based on the fuzzy pattern.

 

Thanks for reading this blog.