cancel
Showing results for 
Search instead for 
Did you mean: 

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

rui_bessa
Discoverer
0 Kudos
1,017

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!

Accepted Solutions (0)

Answers (2)

Answers (2)

Ajit_K_Panda
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
Discoverer
0 Kudos

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 😊

Dinu
Active Contributor
0 Kudos

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.