Filter Gateway Date with SAP CAP CQL

[jmtrujillogalan]

Hi everyone,

I'm trying to filter Date in Gateway system using SAP CAP CQL notations with Nodejs.

https://cap.cloud.sap/docs/node.js/cds-ql#where

When i built my where condition like this:

SELECT.from(X).where({ Date: { between: min, and: max }})

The URL generated was: (...Date gt datetime'xxxx' and Date lt datetime'xxxx ) but the standard filter in gateway (io_tech_request_context->get_filter) doesn't support these operators. It's neccesary to use 'le' and 'ge'.

Is there any solution?

Thanks and Regards

[jmtrujillogalan · Accepted solution]

Finally I declined to use filter with objects and I created the dinamyc where condition with string interpolation.

queryWhere = addWhereCondition(queryWhere, 'DeliveryCode', aDeliveries)
queryWhere = addWhereCondition(queryWhere, 'ActualGI', req.data.ActualGIFrom, req.data.ActualGITo)

function addWhereCondition(sWhere, sProperty, aParam, aParamTo) {

    if (!aParamTo) {
        if (sWhere == ``) {
            if (aParam.length == 1) {
                sWhere = ` ${sProperty} = ${aParam}  `;
            } else {
                sWhere = ` ${sProperty} in (${aParam})  `;
            }
        } else {
            if (aParam.length == 1) {
                sWhere = ` ${sWhere} and  ${sProperty} = ${aParam}  `;
            } else {
                sWhere = ` ${sWhere} and  ${sProperty} in (${aParam})  `;
            }
        }
    } else {
        if (sWhere == ``) {
            sWhere = ` ( ${sProperty} >= ${aParam} and ${sProperty} <= ${aParamTo} ) `;
        } else {
            sWhere = ` ${sWhere} and ( ${sProperty} >= ${aParam} and ${sProperty} <= ${aParamTo}) `;
        }
    }
    return sWhere;
}

I hope it helps you!

Regards

[gregorw]

Have you tried to expose the entity in CAP and set a breakpoint in the handler and check what req.query contains when you try your required query via a OData V4 request?

[jmtrujillogalan]

Hi Gregor

I did and this is the query generated by v4 and v2 OData. It's works fine with mock data and localhost server.

But when I try to connect with SAP Gateway I got a bad request because the URL doesn't contain parentheses (xpr: missing) and it have 'Z' at end of Datetime.

With the function cds.parse.expr I got this query:

cds.parse.expr(`(DeliveryCode = '1') and (ActualGI >= '2022-01-01T00:00:00' and ActualGI <= '2022-01-01T00:00:00')`)

{
  xpr: [
    { xpr: [ { ref: [ 'DeliveryCode' ] }, '=', { val: '1' } ] },
    'and',
    {
      xpr: [
        { ref: [ 'ActualGI' ] },
        '>=',
        { val: '2022-01-01T00:00:00' },
        'and',
        { ref: [ 'ActualGI' ] },
        '<=',
        { val: '2022-01-01T00:00:00' }
      ]
    }
  ]
}

I couldn't generate this "xpr" with the where condition because only with :or object is created.

{ DeliveryCode: { in: ['1','2'] }, and {ActualGI:{'>=':'2022-01-01T:00:00:00'}, or: {ActualGI:{'<=':'2022-01-01T00:00:00'} }}};

Thanks and Regards

[gregorw]

Have you set odata-v2 as kind for your service definition in the package.json (see Import API Definition)?

[jmtrujillogalan]

Hi Gregor

Thanks for your reply, I didn't have this value and for this reason i was adding 'datetime' manually. Your reply helped me to use cds.parse.expr and to see other option to build an uri.

I'm looking for this filter:

$?filter=(DeliveryCode eq '1' or DeliveryCode eq '2') and (ActualGI ge datetime'2022-01-01T:00:00:00' and ActualGI le datetime'2022-01-01T:00:00:00')

However I can't get a valid url to call Gateway, this is my where condition:

{ DeliveryCode: { in: ['1','2'] }, and {ActualGI:{'>=':'2022-01-01T:00:00:00'}, or: {ActualGI:{'<=':'2022-01-01T00:00:00'} }}};

Only with 'or:' condition the tool generates an xpr (parentheses) necessary in Gateway

Regards