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

[rui_bessa]

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!

[Ajit_K_Panda]

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/1.... May be this helps!!

Best Regards, Ajit 

[rui_bessa]

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 😊