Enhance the cds.ql page with more examples

[nocin]

Hi CAP Team,

when I need to look up a specific cds.ql syntax, I always go through my bookmarks instead of looking in the official CAP docs, because the docs are a bit limited on this topic.

There are already some great collections. Perhaps their content can be integrated in the CAP docs or at least linked to them?

• Getting started with cds.ql in NodeJS for CAP - 101 on how to query data
• #SAPCAP: Understanding CQL Queries (Node.js) or the related github repository
• Enhance your cds.ql skills in NodeJS for CAP - projection functions

And I guess there are a few more I missed.

The cds.ql page should of course be clear and not too long, but perhaps a larger number of examples can be listed in another subpage? This would also encourage community members to contribute further or missing query examples via pull request. Especially advanced cases like deep reads, deep reads with a filter, etc. could need some more examples.

Thanks!
nico

[renejeglinsky]

Hi Nico,
let me read through your references. I'm sure I'll get back to you with some questions. As easter holidays are coming up, expect a response by end of next week :)

Thanks René

[renejeglinsky]

Hi @nocin ,
I checked all the links and some of them I knew already. I'll take your idea of having a collection of (more detailed/advanced) examples into our discussions.
Allow me one question: How do the reference docs and those examples work together, from your point of view? Do you still consult the reference docs when you found an example and you want to adapt it to your scenario? Or have have you first read the docs and now examples are just fine? Just trying to understand the "journey" a little bit more :)

All the best
René

[nocin]

Hi @renejeglinsky,

thanks for getting back on this.
As I'm working now with CAP for about 3 years, I already know what I can find in the docs and what not. That's why I most of the time directly search in the blog posts, if I'm not 100% clear about the syntax. In this case, I don't visit the CAP docs at all. Of course, that's just how I do it.
But perhaps it helps to think this from the development direction. We have a rather complex data model with a lot of Associations and Compositions. This topic is completely missing in the cds.ql part of the docs, but I guess in real world scenarios (more complex than the bookshop sample) this is quite common. Some more syntax examples for typical SQL where conditions would be helpful too, like

• { 'in': myArray }
• { 'not in': myArray }
• { 'like': '%test%' }
• { or: { field1: { '>=': 900 }, field2: { '<=': 200 } } }

I think that are usual things a developer needs but has to look up, because the syntax is quite different from every other language where you can write SQL queries. Also, I've read somewhere that SQL joins are not possible in cds.ql, but couldn't find this in the docs right now when checking again.

I'm really no expert when in comes to writing documentations, but at least I can name a few things that I currently miss when using it. :-)

BR,
Nico

[nocin]

Hi @renejeglinsky,

In the last few days I have stumbled across three CQL problems for which I could not find much in the Docs.

1. Does UPSERT support deep payloads?
   Following this blog, it seems like not, but with blogs you never know if they are outdated.
   "Upsert for CAP Node.js does not (unlike CAP Java) support deep payloads."
2. Examples using subqueries with EXIST and NOT EXIST.
   The only example is this one here which is rather intended to show the use of an alias.
3. How to check if an Association/Composition exists/not exists. For example, when having the following entity, how can we select all Files entries, where a thumb is missing.

entity Files : cuid, managed {
    thumb: Composition of one Thumb;
}

I found out, that this can be done, by creating another view.

view MissingFileThumbs as
            SELECT FROM Files {ID} WHERE NOT EXISTS thumb;

But how can the same be done using node cql?

const missingThumbs = await SELECT.from(Files).where({thumb: null})

[cds] - Error: An association can't be used as a value in an expression

I hope the examples help to give an idea of what I look for in the documentary "in my everyday life". :-)

BR,
Nico

[renejeglinsky]

Hi @nocin ,
this is definitely interesting! Thanks for sharing.
We're working on this topic. I didn't give you a heads-up, sorry. We're looking into creating a separate page only for examples and try to make it accessible as CAP Notebook, so that you get a playground to try those examples. Hope that will work out. Will keep you posted and share the PR here as soon as we have some basic things.