Task 10 – The power of CDL with as-select (July Developer Challenge – “Reverse APIs”)

By /

This is a task in the July Developer Challenge – “Reverse APIs”.

In this task you’ll go on a short but hopefully enlightening journey into views and projections, to see how you can elevate SQL concepts to the service definition level in your CDS model, with the power of CDL. You will add a “single expression” entity to your Northbreeze service, which should provide the response required without any implementation at the code (Node.js or Java) level.

Background

In Capire, the Views and Projections part of the section covering Core Definition Language (CDL) introduces us to ways of deriving new entities from existing ones by projections, like views in SQL. Two variants are described:

  • as select from
  • as projection on

If you’ve started with sample CAP services, you’re very likely to have come across as projection on as it’s near ubiquitous in those samples.

But what about as select from, what’s that all about? Well it’s the “batteries included” approach to deriving a view, with all the power of SQL in its CAP-enhanced form, i.e. CQL.

If you want to peruse some examples, use the power of GitHub’s excellent search facility:

org:sap-samples language:cds “as select from”

Again, the cds REPL may come in handy here for experimenting with CQL. How about this, with the basic Northbreeze service (this experiment will make sense in the requirements section next):

await cds.test()
const { Products } = cds.entities
await SELECT `count(ProductID)` .from (Products)
Paste that into a cds REPL and see what you get! For example, put these three lines in a file, e.g. repl-session.js and then paste it into a new REPL like this:
cds repl < repl-session.js
and you should see something like this:
; cds repl < repl-session.js
Welcome to cds repl v 8.0.3
> const server = await cds.test()
[cds] - loaded model from 2 file(s):

  srv/main.cds
  db/schema.cds

[cds] - connect to db > sqlite { url: ':memory:' }
  > init from db/data/northwind-Suppliers.csv
  > init from db/data/northwind-Products.csv
  > init from db/data/northwind-Categories.csv
/> successfully deployed to in-memory database.

[cds] - using auth strategy {
  kind: 'mocked',
  impl: '../../../../../../../usr/lib/node_modules/@sap/cds-dk/node_modules/@sap/cds/lib/auth/basic-auth'
}

[cds] - using new OData adapter
[cds] - serving northbreeze { path: '/northbreeze' }

[cds] - server listening on { url: 'http://localhost:46113' }
[cds] - launched at 7/26/2024, 2:18:05 PM, version: 8.0.3, in: 663.929ms
> const { Products } = cds.entities
> await SELECT `count(ProductID)` .from (Products)
[ { count: 77 } ]

The requirements

Here are the specific requirements for this task.

Define a new entity at the service layer, in your northbreeze service. This entity should be called TotalProducts and defined using the as select from variant as mentioned earlier. It should return a count of the number of products, like this:

{
  "@odata.context": "$metadata#TotalProducts",
  "value": [
    {
      "count": 77
    }
  ]
}

Note the shape of the payload response, in this JSON representation – the value property is an array with a single element, which is an object, which itself has a single property which is count. The type of that property’s value is of course an integer.

If you find yourself implementing anything in your services.js file, think again 🙂

That’s it!

Submitting to the TESTER

Now you’re ready to submit your CANDIDATE service, with this new API endpoint, to the TESTER!

The payload

The task identifier you need to supply in the payload of your submission is: northbreeze-TotalProducts.

You’ll have already done this sort of thing previously so just head back there for the more detailed instructions if you need them, or to the the section titled “The Tester service, and making a test request” in the main challenge blog post.

You’ll need to submit a JSON payload like this:

{
  "communityid": "<your-community-id>",
  "serviceurl": "<the-URL-of-your-service>",
  "task": "northbreeze-TotalProducts"
}

And, just as with the previous (and all further tasks):

  • the value for the communityid property should be your ID on this SAP Community platform (e.g. mine is “qmacro”)

  • the value for the serviceurl property should be the absolute URL (i.e. including the scheme), of your CANDIDATE service which contains the API endpoint (see ℹ️ A note on URLs and services).

That’s it!

Logging of test results

Remember that you can check on your progress, and the progress of your fellow participants – all requests are logged and are available in an entity set served by the TESTER service. The entity set URL is https://developer-challenge-2024-07.cfapps.eu10.hana.ondemand.com/tester/Testlog and being an OData V4 entity set, all the normal OData system query options are available to you for digging into that information.

Until the next task, have fun, and if you have any questions or comments, leave them below!

Scroll to Top