Skip to Content
0
Jul 16, 2020 at 12:50 PM

CAPM: Fuzzy search and use of view from custom handler on HANA

241 Views


Dear Colleagues,

I'm trying to use the fuzzy search functionality in my CAP project and tried to do the following:

1) Build query using

let query = cds.parse.cql("SELECT from BLOCKS { *, TO_DECIMAL(SCORE(),3,2) as score } WHERE CONTAINS(TEXT,'TargetText',FUZZY(0.8,'textSearch=compare,andThreshold=0.3'))");

But looking at internal function _handleContains() I see that fuzzy is not supported, only LIKE statement.

2) I've created .hdbview

VIEW BLOCKSFUZZY (in SEARCH NVARCHAR(5000)) AS SELECT BLOCK_ID 
FROM BLOCKS
WHERE CONTAINS(RAWTEXT, :SEARCH, FUZZY(0.8,'textSearch=compare,andThreshold=0.3'));

And corresponding entity in schema and service

schema.cds

entity BlocksFuzzy(SEARCH : String) {
    key block_ID : UUID @odata.Type;
}

service.cds

entity BlocksFuzzy(SEARCH: String) as select from BlocksFuzzy(SEARCH: :SEARCH) {*};

With these I've managed to have an OData entity set that I can request using and it is working

../BlocksFuzzy(SEARCH='TargetText')/Set

Now I try to use this functionality in one of my custom handlers and I cannot execute select on the parametrized entity:

SELECT.from(BlocksFuzzy).where({SEARCH: {"=": 'Text'}})
SELECT.from("BLOCKSFUZZY('TargetText')")
and etc..

Please provide a possible solution in this case.

I would really appreciate any input on how to use HANA fuzzy search in CAP.