Question Re: CQL & building SELECT .WHERE dynamically in Technology Q&A

CQL & building SELECT .WHERE dynamically

StevenDeSaeger — Wed, 03 Nov 2021 16:48:58 GMT

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

Re: CQL & building SELECT .WHERE dynamically

tobias_steckenborn — Thu, 04 Nov 2021 07:02:33 GMT

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 )

Re: CQL & building SELECT .WHERE dynamically

StevenDeSaeger — Thu, 04 Nov 2021 09:27:30 GMT

Hi Tobias,

Thanks for your response.

Your nifty ES6 JS code in the ends leads to a string containing a 'resolved' where statement correct ?

I triedd the same but with some basic IF statements like

If ( condition ) {
   whereStatement = whereStatement + `additional ${...}`;

but in the end you then try to apply them

.where `{$whereStatement}`

but this does not work. The CQL does not seem to recognise this and just renders no where clause at all.

So the SELECT is executed without where.

Also tried something like

q1 = q1 + .where ``
if ( condition )
q1 = q1.where + ``

None of that really seems to work. The where is only rendered when providing it a direct `` string.

Hence my question.

Kind Regards,

Steven

Re: CQL & building SELECT .WHERE dynamically

tobias_steckenborn — Thu, 04 Nov 2021 09:55:22 GMT

That's correct. The constant whereQuery would contain a string. I don't know if the string of my example would be correct, but you should be able to build a correct one ;).

I've got a short follow-up question on your reply though. You wrote:

.where `{$whereStatement}`

Was that a mistake here or did you write the very same in your code? The dollar sign and the opening curly brace should be switched `${value}`, e.g.:

.where `${whereStatement}`

I don't know if I receive a notification if you don't mention me. Just saw your reply by random as you didn't comment on my reply :D.

Best regards,

Tobias

Re: CQL & building SELECT .WHERE dynamically

tobias_steckenborn — Thu, 04 Nov 2021 10:23:59 GMT

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.

Re: CQL & building SELECT .WHERE dynamically

StevenDeSaeger — Thu, 04 Nov 2021 11:15:26 GMT

Hi tobias_steckenborn ,

I was indeed using the correct statement - just typed it wrongly here - sorry for the confusion.

.where `${whereStatement}`

The variable holds a correct where statement value - just like the one you generated.

Still this is not working and there is in fact no WHERE registered at all for the SELECT statement.

Kind Regards,

Steven

Re: CQL & building SELECT .WHERE dynamically

tobias_steckenborn — Thu, 04 Nov 2021 12:48:55 GMT

If it's already a correct string did you already try using .where(whereStatement)?

Re: CQL & building SELECT .WHERE dynamically

StevenDeSaeger — Thu, 04 Nov 2021 15:59:01 GMT

Hey Tobias,

Yeah just tried adding the statement using

.where(whereStatement)

=> Error with 'unknown From' ...

This is vary puzzling as exact the same statement works when executing with the

.where(`${whereStatement}`)

That gave me the idea on a potential version issue as I noticed I was still using cds 5.5.1 ... so upgraded to latest version 5.6.1 and now I am even more in a world of pain because now 'cds run' just dumps in some standard code ... it really never ends with this stuff.

Re: CQL & building SELECT .WHERE dynamically

StevenDeSaeger — Fri, 05 Nov 2021 14:14:32 GMT

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

Re: CQL & building SELECT .WHERE dynamically

glauco — Sun, 07 Jan 2024 11:48:00 GMT

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