cancel
Showing results for 
Search instead for 
Did you mean: 

SAP CAP - How to do Join

sapdeveloper_
Explorer

Hello Everyone,

I am trying to join two tables/entities using NodeJS as a language.

I got below mentioned error

I tried the solution mentioned in the following URLs

https://answers.sap.com/questions/13038904/how-to-performing-a-join-using-cqn-using-fluid-nod.html

https://answers.sap.com/questions/13006576/cap-cds-nodejs-create-dynamic-join-in-custom-logic.html

It works only when I hardcoded the full table name. But when I try to pass the variable, it is not working

Below is the code which is not working ( Here I am passing variables)

const db = await cds.connect.to('db')
const { tableA, tableB} = db.entities('namespace')
 let query = SELECT.from(tableA)
                    .join(tableB)
                    .on('tableA.ID','=','tableB.ID')
let results = await cds.run(query)

Below is the code which is working ( Here I am passing full table name instead of variable)

 let query = SELECT.from('namespace.tableA')
                    .join('namespace.tableB')
                    .on('namespace.tableA.ID','=','namespace.tableB.ID')
let results = await cds.run(query)

Kindly advise

Regards,

Sandeep

Accepted Solutions (1)

Accepted Solutions (1)

OlenaT
Advisor
Advisor

Hi Sandeep,

to avoid using full names in joins you can set aliases. Unfortunately there is no API that allows to do it, but you can try the following workaround:

const db = await cds.connect.to('db')
const { Books, Authors } = db.entities('CatalogService') 

 let query = SELECT.from(`${Books.name} as B`)
    .join(`${Authors.name} as A`)
    .on('B.ID','=','A.ID')

let results = await cds.run(query)

Please let me know if it helps with your issue.

Best regards,

Olena

sapdeveloper_
Explorer
0 Kudos

Hello Olena,

Thanks for your assistance. Now it is working.

Kindly guide me from where I can read about such workarounds.

Regards,

Sandeep

arminhatting
Participant
0 Kudos

Hello Olena,

what would this query look like in Java?

Best regards,

Armin

Answers (4)

Answers (4)

vLeonkev
Employee
Employee

Guys,

Please consider also the option to have a CDS view on top of your tables and work with it directly instead of doing complex joins in the runtime.

Best

Vladislav

patricebender
Product and Topic Expert
Product and Topic Expert

Don't do joins by yourself. You should use path expressions which are essentially forward declared joins. Those are supported with @sap/cds v7^ if you are using the new cap-js database services.

Ajit_K_Panda
Product and Topic Expert
Product and Topic Expert
0 Kudos

Have a look at this blog: https://blogs.sap.com/2023/05/15/sapcap-understanding-cql-queries-node.js/
I have written for Node Js. But this should apply for Java as well.

abhianuj
Associate
Associate
0 Kudos

Hi all,

I am trying to do join in Java (by writing a CqnSelect Query), but there is no documentation on how we can do it.
The above shared one is in node and is not the same for Java.

Anyone who might know how to do it in java, please let me know.