How to use fuzzy search in cds query

[sander_star2]

In a UI5 app some filters are set like

new oFilter("lastName", oFilterOperator.Contains, oParams.name),
new oFilter("firstName", oFilterOperator.Contains, oParams.name),
new oFilter("companyName", oFilterOperator.Contains, oParams.name)

In the cds entity fuzzy search is enabled on database level like

@sql.append: 'FUZZY SEARCH INDEX ON' 
firstName : String default ''; 
  
 @sql.append: 'FUZZY SEARCH INDEX ON' 
lastName : String default ''; 
  
 @sql.append: 'FUZZY SEARCH INDEX ON' 
companyName : String default '';
  

This is working on database level in Hana with queries like

SELECT * FROM Dummy where CONTAINS(lastName, 'Companie', FUZZY (0.8))
  

But how should you invoke this on a cds query?

[Cguttikonda · Accepted solution]

Hello Sander,

In case of SAP CAPM, the fuzzy search has to be implemented at the service level by altering the req object.

Do not see an option to send the filter parameter for fuzzy search from UI5 and this has been achieved by implementing the custom logic at the service layer.

[sander_star2]

Another solution is like the one below.

define function in service cds

Like

function getSearchDummy(search : String) returns array of Dummy;

define function in service js

Like

          this.on("getSearchDummy", async (oReq) => {

            const oTx = oDb.tx(oReq);

            const sSearch = oReq?.data?.search;

            let query;

            switch (oCds.db.kind) {

                case "hana":

                    query = cds.parse.cql(`SELECT * from Dummy WHERE CONTAINS(companyName, '${sSearch}', FUZZY (0.8)) OR CONTAINS(firstName, '${sSearch}', FUZZY (0.8)) OR CONTAINS(lastName, '${sSearch}', FUZZY (0.8))`);

                    break;

                case "sqlite":

                    query = cds.parse.cql(`SELECT * from Dummy WHERE companyName LIKE '%${sSearch}%' OR firstName LIKE '%${sSearch}%' OR lastName LIKE '%${sSearch}%'`);

                    break;

                default:

                    return new Error(`Unsupported DB kind --> ${this.db.kind}`);

            }

            const aData = await oTx.run(query);

            return aData;

        });

ui5 code

    const sValue = "..." // TODO fill search

            const sKey = "getSearchDummy";

            this.getModel().read(`/${sKey}`, {

                urlParameters: { search: sValue },

                success: (oData) => {

                    let sData = "";

                    for (const oObj of oData.results) {

                        sData = `${sData}\n${oObj.firstName} ${oObj.lastName} ${oObj.companyName}`;

                    }

                    sap.m.MessageBox.show(sData);

                    console.log(oData);

                },

                error: (oError) => {

                    console.error(oError);

                }

            });