CQL & building SELECT .WHERE dynamically

[glauco]

Hi.

I have a SELECT statement for which I want to 'build' a WHERE statement depending on a number of parameters of my JS function.

I had a look at the documentation https://cap.cloud.sap/docs/node.js/cds-ql#where but I cannot find a proper way of doing it.

Ideally the .WHERE should be 'increased' based on my decisions.

Now I have to resort to this - which is ugly as hell - and not maintainable in the long run.

// Define query
        let q1 = SELECT.one.from(CampaignsSteps).columns("count(up__ID) as count");

        // Build our where depending on the parameters (ugly I know)
        if ( statuses === null && campaignID === null ) {
         
            q1 = q1.where `active = true`;
            
        }        
        else if ( statuses !== null && campaignID === null ) {

            q1 = q1.where `( active = true ) and ( status in ${statuses} )`;
            
        } else if ( statuses !== null && campaignID !== null ) {

            q1 = q1.where `( active = true ) and ( status in ${statuses} ) and ( up__ID = ${campaignID} )`;
            
        }

Tried several approaches including building a string and then doing

.where `{$whereStatement}`

Anybody having better ideas ?

Thanks,

Steven

[glauco]

Hi. Have you solutioned it ?

I have the same problem.

it works only if I use it:

.where `cpudt between ${dtFilterInitial} and ${dtFilterFinal} and status = 'PROCESSED'`
but not if I use: .where `cpudt between ${dtFilterInitial} and ${dtFilterFinal} and ( status = '09' or status = '12' )`

[tobias_steckenborn]

Hey Steven,

What did lead to you giving up the "building a string" approach?

Did you try something in the direction of:

const whereParameters = [{parameter: statuses, query: `status in ${statuses}`}, {parameter: campaignID, query: `up__ID = ${campaignID}`}]

const whereQuery = whereParameters.reduce((previousValue, currentValue) => {
  if (currentValue.parameter) {
    return `${previousValue} and ( ${currentValue.query} )`
  }
  return previousValue
}, "active = true")

<- This example might not be correct. Written on my iPad without access to a proper IDE.

Depending on your inputs you should be able to construct a valid string that you can use in .where `${whereQuery}`.

Examples using the code above:

// let statuses = undefined
// let campaignID = undefined
// => active = true

// let statuses = ['test1']
// let campaignID = undefined
// => active = true and ( status in test1 ) 

// let statuses = ['test1']
// let campaignID = 123123
// active = true and ( status in test1 ) and ( up__ID = 123123 )

// let statuses = undefined
// let campaignID = 123123
// active = true and ( up__ID = 123123 ) 

[tobias_steckenborn]

steven.desaeger I just tested using a string in the cap-sflight codebase. Seems to work. There is probably a problem with the string you constructed or the way you referenced it.

[StevenDeSaeger]

tobias_steckenborn

Interesting that works for you.

Which CDS version are you using ? Might be related to me using a slightly older one ...

@sap/cds: 5.5.4
@sap/cds-compiler: 2.7.0