Task 7 – Using CQL in an unbound function implementation (July Developer Challenge – “Reverse APIs”)

By /

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

This task sees you creating a third service, and a first API endpoint within that service. Unlike the previous two services this service will be a more fully formed OData service complete with some sample data. Ready?

Background

The previous two services in this month’s Developer Challenge, basic and plain, were fairly simple and only contained API endpoints that were based on simple functions and an action. There were no entities defined in the CDS model, no data, and no implicit, out-of-the-box CRUD+Q OData operations involved. Today’s task is where that changes.

Northwind and Northbreeze

For this task and the rest of the tasks this month (there are 4 more after this one), you’ll make available API endpoints that all revolve around a simple data model based off the famous Northwind service, various versions of which are running at https://services.odata.org. As you can see from the OData V4 Northwind service’s service document there are quite a few “collections”, otherwise known by the OData term “entity sets”, including:

  • Categories
  • CustomerDemographics
  • Customers
  • Employees
  • Orders
  • Categories
  • Products
  • Regions
  • Shippers
  • Suppliers
  • …

I like to keep things simple, and often turn to a simplified version that I like to call “Northbreeze” (geddit?), where I just have three entity types, with relations between them:

  • Categories
  • Products
  • Suppliers

I have a live Northbreeze service running at https://qmacro.cfapps.eu10.hana.ondemand.com/northbreeze and this is based on a simple CAP service that I’ve defined and made available in a GitHub repo at qmacro/northbreeze. As you can see, there’s no actual code, there’s just:

And that’s pretty much it. Note that the data reflects as accurately as possible the data in the original Northwind service, i.e. there are 77 products ranging from Chai, in the Beverages category, from the supplier Exotic Liquids to Original Frankfurter grüne Soße, in the Condiments category, from the supplier Plutzer Lebensmittelgroßmärkte AG.

You can (and I recommend that you do) clone the repo and use that as a starting point for the service for this and the subsequent tasks in this challenge.

The requirements

Here are the specific requirements for this task.

The service must be served using the OData V4 protocol, with the (default) path prefix /odata/v4 plus the service name northbreeze, i.e.:

/odata/v4/northbreeze
You must make an API endpoint available that returns a sort of “product information” string. The endpoint has a single integer parameter id and the TESTER will make a call to the endpoint with the value of a product ID (it will be between 1 and 77).

It should return a string, formed thus:

<name of the product> by <name of the supplier>

For example, let’s take the Northbreeze product with the ID 11:

So the call would be made to:

/odata/v4/northbreeze/productInfo(id=11)
and the expected response should be:
{
  "@odata.context": "$metadata#Edm.String",
  "value": "Queso Cabrales by Cooperativa de Quesos 'Las Cabras'"
}
By the way, looking back at the OData query operation URLs for the supplier of Queso Cabrales just now, did you know that for such scalar properties returned for OData query or read operations (CompanyName in this example) you can get the literal value on its own? Just append /$value to the URL, so that

https://qmacro.cfapps.eu10.hana.ondemand.com/northbreeze/Products(11)/Supplier/CompanyName

becomes

https://qmacro.cfapps.eu10.hana.ondemand.com/northbreeze/Products(11)/Supplier/CompanyName/$value

and you get

Cooperativa de Quesos 'Las Cabras'
instead of
{
  "@odata.context": "../../$metadata#Suppliers(5)/CompanyName",
  "value": "Cooperativa de Quesos 'Las Cabras'"
}

The endpoint definition

As you may have worked out by now, this API endpoint needs to be defined as an unbound function (yes, it could be implemented as a function bound to a particular product but we’ll be doing that sort of thing in a later task).

When you have defined the endpoint, you should see evidence of it (productInfo) in the OData metadata, which should look like this (vastly reduced for brevity):

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema Namespace="northbreeze" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EntityContainer Name="EntityContainer">
        <EntitySet Name="Products" EntityType="northbreeze.Products">
          <NavigationPropertyBinding Path="Category" Target="Categories"/>
          <NavigationPropertyBinding Path="Supplier" Target="Suppliers"/>
        </EntitySet>
        <EntitySet Name="Suppliers" EntityType="northbreeze.Suppliers">
          <NavigationPropertyBinding Path="Products" Target="Products"/>
        </EntitySet>
        <FunctionImport Name="productInfo" Function="northbreeze.productInfo"/>
      </EntityContainer>
      <Function Name="productInfo" IsBound="false" IsComposable="false">
        <Parameter Name="id" Type="Edm.Int32"/>
        <ReturnType Type="Edm.String"/>
      </Function>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

The endpoint implementation

The idea of this task is to get you using some CQL. In other words, you’ll have to write a simple implementation for this unbound function, writing a handler for the on hook as usual, for the productInfo event.

CQL

If you’re not sure where to start, I’d recommend the Querying in JavaScript section of Capire, in particular the SELECT class. Within that SELECT section, you may want to pay particular attention to the following properties:

In particular, you’ll have to work out how to get a property of a related entity. In other words, how do you express what you want, using the facilities such as path expressions or projection functions described in the columns section. On this topic, you may find this blog post helpful: Turning an OData expand into a cds.ql CQL query with a projection function in CAP.

Using async/await

One more tip: Up until now, you’ve defined your JavaScript function based handlers for functions and actions in quite a straightforward way. But now you’re about to use the SELECT class, which by itself defines a query. It doesn’t run it directly. If you read the Executing Queries section of Capire you’ll see that “you can just await a constructed query, which by default passes the query to cds.db.run()“. That means you can use await in front of your SELECT expression.

But that in turn means that your handler function needs to be defined as an asynchronous one, with async. If you’re looking for some examples or inspiration, remember that there are some great CAP samples in the sap-samples org on GitHub. And remember also that GitHub has an excellent search mechanism, where you can express detailed contextual searches, like this one:

org:sap-samples language:js “await SELECT” AND (path:/srv/)

Don’t you just love it when you come across a well thought out URL scheme for this sort of search facility? Beautiful.

Setting up for CQL

Note that also, for this handler function and some of the subsequent ones in this service, you’ll need a connection to the database layer, and a ‘handle’ on the Products entity definition (to use in your SELECT expressions, specifically with the .from() method). You can just use this classic pair of constant definitions, before you start to define your on handler:

const db = await cds.connect.to('db')
const { Products } = db.entities

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-productInfo.

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-productInfo"
}
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