SAP CAP - How to do Join

[sapdeveloper_]

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

[OlenaT · Accepted solution]

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_ · Accepted solution]

Hello Olena,

Thanks for your assistance. Now it is working.

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

Regards,

Sandeep

[arminhatting · Accepted solution]

Hello Olena,

what would this query look like in Java?

Best regards,

Armin

[vLeonkev]

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]

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]

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]

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.