Question Re: CAP - How to programmatically order by a certain colu... in Technology Q&A

CAP - How to programmatically order by a certain column when using a path expression in NodeJS?

rui_bessa — Fri, 16 Feb 2024 13:07:02 GMT

Hello experts,
I've been trying different ways of ordering by a certain column on a path expression while using CAP v7 with NodeJS and I have not been successful yet.

I've searched through the documentation but unfortunately was not able to find a similar case to mine.
Given the following example and supposing that the authors projection has a column named age :

let books = await SELECT.from('Books').columns(books => { books `*`, books.authors('*') }).where({ID : 1});

How can I order all the authors from the book with ID = 1 by age ?

I've tried the following:

let books = await SELECT.from('Books').columns(books => { books `*`, books.authors('*').orderBy('age desc') }).where({ID : 1});

But this leads to the following error :
[cds] - TypeError: Cannot read properties of undefined (reading 'age')

Is there any possible way I can order authors by a column in this case?
Thanks!

Re: CAP - How to programmatically order by a certain colu...

Dinu — Sat, 17 Feb 2024 05:25:20 GMT

This might be what you are looking for from syntax perspective:

let books = await SELECT.from('Books').columns(books => { books `*`, books.authors('*')`[order by age desc]` }).where({ID : 1});

This is extended from the syntax for infix filters. But, I'm not sure if that sorts the inner array as you are expecting. My guess is sorting is not yet supported.

Re: CAP - How to programmatically order by a certain colu...

Ajit_K_Panda — Sun, 18 Feb 2024 15:06:20 GMT

Hi @rui_bessa ,

May be you can read from authors while applying order by age and navigate to books with filters.

Different examples are available here: https://community.sap.com/t5/technology-blogs-by-sap/sapcap-understanding-cql-queries-node-js/ba-p/13554662. May be this helps!!

Best Regards, Ajit

Re: CAP - How to programmatically order by a certain colu...

rui_bessa — Tue, 20 Feb 2024 13:39:16 GMT

Hello @Ajit_K_Panda 😊
Thank you for the comment!
I think that with your suggestion, this particular problem would definitely get resolved.
But I've provided a simple case to also simplify the question I wanted to ask - but in reality, my specific case is a bit more complex and I have a query which is quite more complex than the one I wrote here in my post.

I've managed to workaround this by creating views on top of my entities that already come ordered by the column I want, but I really wanted to understand ( and know ) if there's a real way of ordering the way I asked 😊