The blog shows how to achieve fuzzy search and case insensitive by overwriting the CQL (CDS Query Language).

If you are not familiar with CQL, please refer to the link

Story

As a fiori (SAPUI5) developer, most of you knows smart table and smart filter, they provide a rapidly way to customize a inquiry page by using CAP or XML annotations. You can create codeless filter conditions by customizing your CDS file.

But sometimes, those auto-built filter conditions may not fulfill your needs, for example,smart filter only allow user search specific condition with exact case sensitive full string

Alternatively, smart filter provided general search bar, user can achieve fuzzy search or case insensitive search for all of the columns in the smart table.

But seems there is no way to do a fuzzy search or case insensitive search against specific filter.

After some researching and guidance from experts, I found another way, may be make your life easier.

Mechanism

Before discussing the solution, just want to ensure we understand how the fiori send request to database via CDS

Refer to the diagram, basically, fiori view triggers the event function defined in controller.js, then send the request to CDS, and CAP service compile the cds view to a Json format request, then send to database.

As we know, CAP allows cds register API (for more info, please refer to here  ), we can customize an event script before/on/after the cds entity is called.

‘Before event script’ is happened after the json query generated and before sending to database.

So far, I think you may got what we could do in next :), it’s time talk about the solution

Diagram

Solution

First of all, let’s look at what is Json Query looks like, actually, you can easily fetch the query by using this code in your <cds_catelog>.js file

console.log(req.query)

But it returns a very huge json content result. we just need to overwrite the select statement, you can find the where clause is like following, under the json node (req.query.SELECT.where )

[ 
{ ref: [ 'COLUMNNAME1' ] }, 
'=', 
{ val: 'your value1' } ,
and,
{ ref: [ 'COLUMNNAME2' ] }, 
'=', 
{ val: 'your value2' } ,
.......
]

what we need to a new statement with “LIKE” operator and “upper case function” like following format.

Please note that here, the sample value (‘your value1’ ‘your value2’) are convert to upper case ensure it can match to the column properly.

And we add the topper function to convert the COLUMN to upper case.

[
  {
    func: 'toupper',
    args: [ { ref: [ 'COLUMN1' ] } ]
  },
  'like',
  { val: '%YOUR VALUE1%' },

  and,

  {
    func: 'toupper',
    args: [ { ref: [ 'COLUMN2' ] } ]
  },
  'like',
  { val: '%YOUR VALUE2%' },

  .......
]

After you understood the way, we can easily overwrite the json query under ‘Before Event’ API

This is my the sample code in registered API

Please note, I used ‘*‘ (asterisk) to allow this query conversion can be triggered before any of entity under the catalog, if you just want some of entity use the conversion script, then put your entity name.

I put some logic to avoid some condition is convert incorrectly.

this.before ('READ', '*' ,async (req)=>{

        //console.log(req);

        console.log("before searching");

        console.log(req.query.SELECT.where);

        let conditions =req.query.SELECT.where;

        /*console.log(conditions[0].ref);
        console.log(conditions[1]);
        console.log(conditions[2].val);
        */
        //req.query.SELECT.where[1] = 'LIKE';
        //req.query.SELECT.where[2].val = `%${conditions[2].val}%`;


        //req.query.SELECT.where.push('and');
        //req.query.SELECT.where.push({ func: 'contains', args: [ { ref: [ 'KEYWORD' ] }, { val: '20' } ] });

        var newCondition = [];

        if (conditions){

            conditions.forEach((condition,index) => {
            
                var scol ='';
                var sval = '';
                var sOperator = '';
    
                if(condition==='and' || condition==='or'||condition==='('||condition===')'
                || condition.func !=undefined
                ){
                    newCondition.push(condition);
                }
    
                if(condition.ref!=undefined){
                    
                    //console.log(index);
    
                    scol=condition.ref[0];
                    sOperator=conditions[index+1];
                    sval=conditions[index+2].val;
    
                    //newCondition.push({ func: 'contains', args: [ { ref: [ scol ] }, { val: sval } ] });


                    if(sOperator==='='){
                        newCondition.push({ func:'toupper', args:[{ref: [ scol ] }]},'like',{ val: `%${sval.toUpperCase()}%` });
                    }else{
                        newCondition.push({ ref: [ scol ] },sOperator,{ val: sval });
                    }
        
    
                    //newCondition.push(`${scol} like '%${sval}%'`);
                    
                }
            });
        }


        //overwrite the condition
        //req.query.SELECT.where = newCondition;

        if(newCondition&& newCondition.length>0){
            req.query.SELECT.where = newCondition;
        }
        

        console.log(req.query.SELECT.where);

     })

• 1. CAP Service generated CQL is editable
• 2. CQL is not completely same as SQL
• 3. How to overwrite the Json request.

Summary

The CAP service API dose not only offer the way of pre-processing or post-processing of calling odata entity from data utilization perspective, but also showing a way of customizing the system generated request.