query with path expression node.js

[martin_jonsson]

Question about path expressions in queries.

it is stated here that path expressions are supported in queries.

It is also stated here that service provider methods can return cds.ql object.

The cds.ql object is the same as a CQL object, which is an AST of a parsed CDS query. The parsing is done with the ```cds.parse.cql``` method that only accepts the CDS sql statement as input. This means it can't work with path expressions since the definition is in the association on the model and the model is not known to the parser. Sure enought, the output does not contain a join clause or mixin.

Later when the CQL object is converted to SQL, the model is there but the reference parser is not checking the model for association paths.

Mixins are correctly in the AST / CQL but in the cds-sql (converter) this feature is not supported.

So how do I use path expressions in queries? Add a model to the query parser, alternatively correct the sql converter to read the association? Or if not yet possible, as with mixins, when is this feature planned?

If an example is needed, just run this query on the standard bookshop model:

cds.parse.cql("SELECT ID FROM Books.author");

It will give you an error that table Books_author does not exist if using sqlite. Since the reference parser does not know that author is an association and not table name.

Many thanks Martin

[former_member631165]

Hi,

i think this is what you are looking for:

SELECT.from('Books').columns('author.ID')<br>

Regards

Samuel

[former_member631165]

Sorry wrong link, please have a look here:

https://cap.cloud.sap/docs/node.js/api

Search for section 'SELECT .from (entity, columns?)' there is another sample.

[martin_jonsson]

Hello Samuel. This is a different animal all together. It gets really interesting when you do:

SELECT.from("books").columns((proxy)=>{
  proxy.author.id.as("cute_alias");
})

And use the power of the javascript proxy object. However, this merits a discussion elsewhere.

[martin_jonsson · Accepted solution]

I close this question with the understanding that these features are not yet available for node.js. I will add another question to ask for the roadmap.

Thank all for your help! Martin