CAP NodeJS remove implicit sorting on primary key

[akshat_malhotra]

Hello ,

We are facing an issue where every service defined in CAP (Nodejs module) returns the result set in sorted order of primary key by default . We want to remove this and provide our own custom sorting . However writing an order by statement after entity select is not causing any change to the output result set and is still sorted by primary key .

From cap documentation i have read that there is some implicit sorting mechanism involved here . In JAVA runtime we can remove the implicit sorting by adding cds.query.implicitSorting.enabled: false in some config file . (https://cap.cloud.sap/docs/java/development/properties) . How do we achieve the same in NodeJS environment? Please provide the full syntax if possible as i tried adding the same as a nested structure in package.json and it didnt work .

Regards,

Akshat Malhotra

[akshat_malhotra]

This only works as of now for us using a custom on after handler on service and sorting the json response on sort column (javascript object sorting) .

async function _afterReadServiceHandler(res,req) {

res.sort((a, b) => (a.SORTORDER > b.SORTORDER) ? 1 : -1)

}

Please note : writing order by query in on before handler does not work as the default behavior of sorting on key gets applied anyway .

[former_member631165]

Hi,

Check this documentation about implicit sorting:
https://cap.cloud.sap/docs/guides/generic#implicit-sorting

The key is always the default sort order, but if you specify other orders yourself, they have a higher priority.

BR
Samuel

[akshat_malhotra]

This did not work as i mentioned that the custom order given in cds file was completely ignored . Not sure why

[former_member631165]

Hi,

what exactly did you try? There is a sample like this:

service CatalogService {
  entity Books as projection on my.Books order by title asc;
}

Did you try this?

[akshat_malhotra]

Writing order by clause after projection gave a syntax error . So i tried this :

service catalogService{

entity solVersion as select from table {*} order by SORTORDER;

}

This did not give any syntax error but also did not work .

[epamtiosteel]

Good day, sambr

what should I do if a key of an external service defined by edmx/cds is annotated by sap:sortable="false"

I have a read-only access to this service (SuccessFactors sandbox)

so I'm receiving an error error.Error during request to remote service:

[COE0003]Bad property expression: backgroundElementId; backgroundElementId is not sortable

[former_member631165]

For an external service you need a custom handler anyways, so you could adjust the req.query before forwarding it to the external service, so it is not sorted anymore.

[WouterLemaire]

I would expect that adding orderby to your odata request would work...

A workaround could be the create an after read eventhandler and do the sorting yourself in js: https://cap.cloud.sap/docs/node.js/services#event-handlers

But maybe there is a beter way to do this...

[sergei-u-niq]

After-read wood be too late - especially in combination with top/skip.

This should be done in before-read: if no orderby is contained in the query, add your own orderby. This way, your custom order-by is executed on DB level. Make sure not to modify the SELECT if a single-read is executed.

[WouterLemaire]

Agree the earlier the better. Based on the question I assumed adding order by is being ignored by the db.

[akshat_malhotra]

I am not a UI expert but I understand there is some challenge calling service with $orderby especially in smart tree table control . By using handlers I understand could be the only way for now . I was just wondering if there is an option to just turn the implicit sorting on primary key off and specify the custom order by clause within service implementation file(.cds) itself , thereby avoiding writing handlers .

eg. entity as select from <table> {*} order by SORTCOLUMN asc . Currently this order by clause is being ignored for some reason .

[former_member423812]

Try using this annotation https://github.com/SAP/odata-vocabularies/blob/master/vocabularies/Common.md#SortOrder