CAP CDS NodeJS Create dynamic join in custom logic

[WouterLemaire]

Hi Experts,

I have a requirement to create a dynamic query in CAP. For this I'm using NodeJS as a middlelayer with custom logic. In this custom logic I want to join two tables but based on the filters the tables in the join can be different. I want to use the CAP CDS syntax to build a query like this: "SELECT * from Foo INNER JOIN Bar on Foo.a = Bar.a"

Parsed from the live tester https://cap.cloud.sap/docs/04-CDS/pegjs/:

SELECT: 
  columns: ["*"]
  from: {join: inner, args: [{ref: [Foo]}, {ref: [Bar]}], on: [{ref: [Foo, a]}, =, {ref: [Bar, a]}]}

I want to build the same join by using Query Notation. It is in the documentation but I'm missing an example:

https://cap.cloud.sap/docs/cds/cqn#joins

What I have tried so far:

let query = {join:"natural",sources:[Table1Source,Table2Source],on:"Table1Source.A= Table2Source.A"}

and

let query = SELECT.from({join:"natural",sources:[Table1Source,Table2Source],on:"Table1Source.A= Table2Source.A"})

Anyone experience building a join in the nodejs custom logic with CAP?

Thank you in advance!

Kr, Wouter

[WouterLemaire]

My question is how can I do this with the NodeJS API instead of the Query Notation?

[david_kunz2 · Accepted solution]

Dear Wouter,

You can do the following:

 SELECT.from('A')
          .join('B')
          .on({ 'A.x': { '>': 'B.y' } })
          .and({ 'A.x': null })

and

SELECT.from('Null')
        .join('One')
        .on('Null.a', '=', 'One.a')
        .leftJoin('Two')
        .on('One.a', '=', 'Two.b')
        .rightJoin('Three')
        .on('Three.b', '=', 'Null.a')
        .join('Four')

However, these APIs are not yet documented.

Best regards,
David

[WouterLemaire · Accepted solution]

Thanks! Indeed missing some docu about this 🙂

[WouterLemaire · Accepted solution]

And what if you use sources to make the join instead of strings for the names of the tables? What should I use in the on condition?

For example:

const {table1, table2 } = cds.entities("be.wl.tables");
SELECT.from(table1)
  .join(table2)
  .on({ 'table1.x': { '=': 'table2.y' } })

[arminhatting · Accepted solution]

Hi David,

thank you for the solution in Nodejs.

Do you know if this functionality is also available in Java?

I can't find a hint in the documentation.

Best regards,

Armin

[david_kunz2 · Accepted solution]

Hi Armin,

Unfortunately I don't know if Java can do something like that, I will forward this to the Java colleagues.

Best regards,
David

[david_kunz2]

Hi Wouter,

Then you need to provide the name of the table.

Hint: If you use the CDS REPL, you can easily play around with it.

>> cds r

Best regards,
David

[pascal_wasem1]

david.kunz2 just came across this.

So with latest @sap/cds (6.7.0 at this point of time) there is no official documentation for any "join" API:

https://cap.cloud.sap/docs/node.js/cds-ql#SELECT

So is this still applicable / advised and safe to use?

Thank you in advance,

Pascal