cancel
Showing results for 
Search instead for 
Did you mean: 

How to use fuzzy search in cds query

sander_star2
Explorer
0 Kudos
751

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?
View Entire Topic
sander_star2
Explorer
0 Kudos

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);
                }
            });
David21
Explorer
0 Kudos

I am using hana DB, When i use the following with OR it doesn't work:

 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))`);

But the interesting thing is that it works with AND i.e. returns the entity that has the value in both columns. Any ideas why it isn't working with OR.

 

sander_star2
Explorer
0 Kudos
To my opinion there should be no issue using OR or AND. If AND returns values OR should at least also return data (less strict). Maybe there is a syntax issue. I should try it again.
David21
Explorer
0 Kudos
I have tried using the 'or' operator in local hana db, it works there. But when I try to query external API , only 'and' operator works there.
David21
Explorer
0 Kudos
I have also tried using the following syntax but got the same result with it as well: let where= [{func : 'contains', args : [{ref : ['ID']},{val : req.query.SELECT.search[0].val }]} , 'or' , {func : 'contains', args : [{ref : ['name']},{val : req.query.SELECT.search[0].val }]} ] req.query.SELECT.where = where