query options in function to database query

[akuller]

Hello all,

I have defined a function that supports a filter. So far I access the filter parameters using req._queryOptions and build the select accordingly. My problem is that an additional requirement would now make this too complex.

function getNearbyPoints(latitude : Decimal, longitude : Decimal)  returns array of Records;

How can I pass the query options from the request directly into the query of the database?

I did find a topic on this here in SCN, but it is older and there was no answer.

Thanks a lot

[martinstenzig]

Cab you provide more details as to what you have in mind regarding query options? Is it just straight key value combinations or additional functionality such as filters, field selections etc?

Something like this?

http://localhost:4004/community-example/getNearbyPoints(latitude=1232.23,longitude=212.23)?Test=12¶2... eq 'example record'

And then what do you want to do with the query options on the database?

[akuller]

Hi martin.stenzig3 ,

Exactly, it's about $select and $filter.

Based on the coordinates, points in the surroundings are determined (with a spatial function). At the moment all are returned and filtered by type, here I look into $filter myself and build the condition. Now more fields will be added, which makes it more complex to build. My thought was, if a standard entity can do all this itself, there must be a possible way to do it.

[martinstenzig · Accepted solution]

Try this (Full disclosure, this is not an officially supported interface):

const odata2cqn = require('@sap/cds/libx/odata/parser').parse

const o2c = odata2cqn(decodeURI(req.req.originalUrl))

That will parse your complete URL into a CQN and might have too much details, but you can strip out in the object what you don't need.

[akuller · Accepted solution]

Hi martin.stenzig3

wow great, thank you very much. Too bad it's not a released function. The code is much nicer to read that way.

I have adapted your snippet a bit, I call the function in a batch request. Additionally I have defined a basic query and extend it with the requested options.

    this.on('getNearbyPoints', async (req) => {
        try {
            const p = new wkx.Point(req.data.longitude, req.data.latitude).toWkb()
            var query = SELECT.distinct.from(...)
                .columns...
                .orderBy`distance ASC`
                .where ...
                .limit(50)
            const functionURL = decodeURI(req._.odataReq._inRequestUrl),
                odata2cqn = require('@sap/cds/libx/odata/parser').parse,
                functionQuery = odata2cqn(functionURL)
            functionQuery.SELECT.columns.forEach(element => {
                const index = query.SELECT.columns.findIndex((obj) => (!!obj.ref && obj.ref[0] === element.ref[0]) || obj.as === element.ref[0]);
                if (index === -1)
                    query.SELECT.columns.push(element)
            })
            query.SELECT.where.push('and')
            query.SELECT.where = query.SELECT.where.concat(functionQuery.SELECT.where)
            return await query
        } catch (error) {
            console.error(error.message)
        }
    })

[julianSch · Accepted solution]

Hello Martin,

[julianSch · Accepted solution]

Hello Martin, I need this exact same thing but for Java. Do you know if this exists ? Thank you