on 11-03-2021 4:48 PM
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
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 )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
User | Count |
---|---|
74 | |
9 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.