Technical Articles
Computed field example in CAP
In this post, I show one way of using computed properties in CAP, using CDS and service events in Node.js.
Over in the CAP section of the Community Q&A, Pierre Dominique asked an interesting question that I thought I’d have a go at answering in the form of a short post. Here’s the question: Counting association entities using CDS – go ahead and have a quick read of it, then come back here to find out one way of doing it.
Given the bookshop sample data as a basis, how do we extend the service to include a property which indicates, for each author, how many books they have written?
Step 1 – the definitions
Here’s the basic schema. It’s very similar to the example that Pierre gives, but doesn’t have the extra ‘numberOfBooks’ property at this level – I wanted to give myself an extra challenge by not defining it at the data model layer, but defining it only at the service definition layer. If we’re going to have a computed property, we should avoid having it pollute the space at the persistence layer.
namespace my.bookshop;
entity Books {
key ID : Integer;
title : String;
stock : Integer;
author : Association to Authors;
}
entity Authors {
key ID : Integer;
name : String;
books : Association to many Books on books.author = $self;
}
db/schema.cds
It’s possible to add computed properties to an entity at the service definition level. Here’s what the service definition looks like with a computed property for the requirement at hand:
using my.bookshop as my from '../db/schema';
service CatalogService {
entity Books as projection on my.Books;
entity Authors as select from my.Authors {
*,
null as numberOfBooks: Integer
};
}
srv/service.cds
Notice the “as select from”, as opposed to the simpler “as projection on”. It allows us to specify properties, which is what we do in the block that follows:
- the “*” brings in all the existing properties from the my.Authors definition
- then we define a new property “numberOfBooks” as an Integer type
Let’s take a moment to have a look what that produces. Running this at the command line:
cds compile srv/service.cds --to sql
… gives us the schema definition, which includes these two views that have been generated from the two entities defined at the service level:
CREATE VIEW CatalogService_Authors AS SELECT
Authors_0.ID,
Authors_0.name,
NULL AS numberOfBooks
FROM my_bookshop_Authors AS Authors_0;
CREATE VIEW CatalogService_Books AS SELECT
Books_0.ID,
Books_0.title,
Books_0.stock,
Books_0.author_ID
FROM my_bookshop_Books AS Books_0;
Take note of the “numberOfBooks” property in the “CatalogService_Authors” view.
While we’re in the mood for looking at generated compilations, let’s do the same, but this time see what the service definition will look like, in the form of Entity Data Model XML (EDMX) – which you and I know more comfortably as OData metadata.
Running this at the command line:
cds compile srv/service.cds --to edmx
… gives us this:
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
<edmx:Include Alias="Core" Namespace="Org.OData.Core.V1"/>
</edmx:Reference>
<edmx:DataServices>
<Schema Namespace="CatalogService" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityContainer Name="EntityContainer">
<EntitySet Name="Authors" EntityType="CatalogService.Authors">
<NavigationPropertyBinding Path="books" Target="Books"/>
</EntitySet>
<EntitySet Name="Books" EntityType="CatalogService.Books">
<NavigationPropertyBinding Path="author" Target="Authors"/>
</EntitySet>
</EntityContainer>
<EntityType Name="Authors">
<Key>
<PropertyRef Name="ID"/>
</Key>
<Property Name="ID" Type="Edm.Int32" Nullable="false"/>
<Property Name="name" Type="Edm.String"/>
<NavigationProperty Name="books" Type="Collection(CatalogService.Books)" Partner="author"/>
<Property Name="numberOfBooks" Type="Edm.Int32"/>
</EntityType>
<EntityType Name="Books">
<Key>
<PropertyRef Name="ID"/>
</Key>
<Property Name="ID" Type="Edm.Int32" Nullable="false"/>
<Property Name="title" Type="Edm.String"/>
<Property Name="stock" Type="Edm.Int32"/>
<NavigationProperty Name="author" Type="CatalogService.Authors" Partner="books">
<ReferentialConstraint Property="author_ID" ReferencedProperty="ID"/>
</NavigationProperty>
<Property Name="author_ID" Type="Edm.Int32"/>
</EntityType>
<Annotations Target="CatalogService.Authors/numberOfBooks">
<Annotation Term="Core.Computed" Bool="true"/>
</Annotations>
</Schema>
</edmx:DataServices>
</edmx:Edmx>
There’s one thing in there that’s of particular interest – the annotation of this new property. You can either stare at this XML until you see it, or just look at it as extracted from that sea of angle brackets:
<Annotations Target="CatalogService.Authors/numberOfBooks">
<Annotation Term="Core.Computed" Bool="true"/>
</Annotations>
This has been automatically generated from that simple service definition earlier. Thanks, CAP!
Step 2 – the implementation
Implementing the logic to provide values for this computed property is next up. As you may know, we can provide custom logic in the form of functions attached to specific events in the request/response lifecycle as OData operations are processed, and in a very comfortable way, via the “convention over configuration” approach of simply providing a JavaScript file of the same base name as the service definition file, in the same directory.
The Node.js CAP runtime will then discover this file and use it as extra (or overriding) implementation logic. This is what it looks like:
module.exports = srv => {
const { Books } = srv.entities
srv.after('READ', 'Authors', (authors, req) => {
return authors.map(async author => {
const publications = await cds.transaction(req).run(
SELECT .from(Books) .where({ author_ID: author.ID })
)
author.numberOfBooks = publications.length
})
})
}
srv/service.js
(In case you’re wondering: yes, I am trying to avoid semicolons and double quotes, and yes, I like the ES6 fat arrow syntax for functional style, and no, I am not writing and will not write any “class” based code here – in my opinion the whole “object orientation comes to JS” is the wrong direction entirely. Stick that in your pipe and smoke it! :-))
Anyway, here’s what’s going on in the code:
- we grab the Books entity from within the service object
- we hook in a function to be called when READ requests are processed on the Authors entity, specifically after the main part of the request has been fulfilled (i.e. using srv.after)
- that function expects the results of the request fulfilment (i.e. the author(s) retrieved), plus the original request object, from which we can create a context in which to run a CDS query
- the query is within a map function over the authors retrieved, and goes to get the books for that author
The CDS query is made using the CDS Query Language (CQL) fluent API, which I’ve tried to illustrate with some gratuitous whitespace (before .from and .where, in particular).
Once the value for “numberOfBooks” has been computed and assigned, we simply “let go” and the enhanced result set is returned in the response.
Step 3 – profit!
Here’s what this results in, after deploying the definitions and starting the service (I have a few books and authors in some sample CSV files):
=> cds deploy && cds run
> filling my.bookshop.Authors from db/csv/my.bookshop-Authors.csv
> filling my.bookshop.Books from db/csv/my.bookshop-Books.csv
/> successfully deployed database to ./bookshop.db
[cds] - connect to datasource - sqlite:bookshop.db
[cds] - serving CatalogService at /catalog - impl: service.js
[cds] - service definitions loaded from:
srv/service.cds
db/schema.cds
[cds] - server listens at http://localhost:4004 ... (terminate with ^C)
[cds] - launched in: 1126.872ms
And here’s some sample output, retrieved with “curl” and nicely pretty-printed with “jq” (yes, folks, this is all in a terminal, ON THE COMMAND LINE)*:
=> curl -s http://localhost:4004/catalog/Authors | jq
{
"@odata.context": "$metadata#Authors",
"@odata.metadataEtag": "W/\"8q5jjLD6vJ0ARrjnkajTONXIn38vpa1wxoXucua4kzU=\"",
"value": [
{
"ID": 42,
"name": "Douglas Adams",
"numberOfBooks": 3
},
{
"ID": 101,
"name": "Emily Brontë",
"numberOfBooks": 1
},
{
"ID": 107,
"name": "Charlote Brontë",
"numberOfBooks": 1
},
{
"ID": 150,
"name": "Edgar Allen Poe",
"numberOfBooks": 2
},
{
"ID": 170,
"name": "Richard Carpenter",
"numberOfBooks": 1
}
]
}
That’s pretty much all there is to it, at least as far as I can see. I hope you find this useful. I had fun writing it, thanks Pierre for a good question.
*i.e. the future
Hi,
Wow, thanks for this post DJ! Very useful and detailed, as usual.
I have one question though: what are the pros and cons of this approach compared to creating a view (calculation or plain SQL) and computing the number of books in the view and then exposing the view as an (OData) entity? Or maybe doing this at the CDS level (I don't know if this is supported) in the service definition for instance?
I guess that doing this at the DB level would be better in terms of performance but then we would lose some of the abstraction provided by CDS?
Cheers,
Pierre
Great question, and there are pros and cons to each approach. I see that Johannes Vogel has added some info related to this to your question in Q&A, actually, which I will try out myself too later.
I do like the abstraction nature of CDS, the higher up away from persistence, the more generic it is, for me. Of course, that comes at some cost (performance, perhaps), but (a) performance is only an issue if the delays are measurable, and (b) I wanted to explore and show how to access the rest of the data from a service event hook 🙂
I found a way to do it using CDS (just because I wanted to know if this was possible :-)):
cds compile .\srv\cat-service.cds –to sql :
Nice work, Pierre! Hopefully this will also be useful to others.
Hi DJ Adams,
Before to start to talk about my issue, congratulations for your videos "Hands-on SAP dev" live stream series, really nice work and big effort.
Well, I have an authorization issue in my cds nodejs when i uploaded to Cloud foundry and i'm stuck here. Please, can you check what i missed? I want to exposed a CDS in nodejs with a scope associated but i received unauthorized, because when i contacted to the app says... "Anonymous access". Let me explain my config:
this is my service... I created three different to check the authorization in each of them
My cdsrc.json:
And my app protected with route:
and my roles defined in xs-security.json (here i tested with different options in the name with name-subdomain-space or name-subdomain etc...)
And my yaml:
And when i go to my app, i login in my hana trial, but when i'm going to recover the information from datasource says "Forbidden" because its login with anonymous, Why is anonymous? If I'm logging into SAP with my email and the role generated is assigned to me in Cloud foundry. What i missed?
In addition i tried to add in "requires" the name of role created in sap cloud foundry.... but it didn't work
Many Thanks
Hi Javier - thanks very much!
May I ask that you please ask this as a new question on the Q&A section of the community (https://answers.sap.com/tags/9f13aee1-834c-4105-8e43-ee442775e5ce) so that others are more likely to see it too? Thanks. DJ
Hi DJ,
Quick question, for your approach of defining the computed value in the service layer using a "null" field, do you need to modify the POST statement to strip out the computed field?
I tried your approach and I get an SQL error saying "SQLITE_ERROR: no such column: roleName"
By the way. I tried Pierre's approach as well but this does not seem to work for entities that are auto-exposed through a @odata.draft.enabled" declaration on an associated entity.
My Scenario (https://github.com/scongia/or-assistant.git):
Persons.Roles is a composition of many Person_Roles and Persons has drafts enabled.
I have a lookup table called Roles containing role name, description etc.
I would like adminservice.person_roles to return the descriptive name of the role from Roles so that i can bind it to a table
db/data-model.cds
srv/admin-service.cds
Hi Sergio, great that you're looking into this.
Asking a question like this is best done in the Q&A section of the community - do you fancy doing that over there? That way, also, others will see it and either be able to response and certainly benefit from it.
Cheers!
Thanks for the suggestion. I have created a question here https://answers.sap.com/questions/13038785/referencing-a-field-from-a-foreign-entity-in-an-ob.html
Rgds.
Hey qmacro
Is there a similar approach to define/return an association/entityset at service level?
S
Hi there!
I´ve implemented the example exactly as presented but it is not responding as expected. The weird thing is that it sometimes fills the numberOfBooks attribute but most of the times it doesnt. I keep on hitting the refresh button and it keep on changing the results. Here are two responses, one after the other, without reseting or changing anything:
(hit refresh…)
Have you seen it? Edgar Allan Poe who once had 2 books the second time has none. Emily had one and now has none. The others didn´t showed up even the first time. Sometimes I get books for all of them, sometimes for some, but most of the times for none. Straaaange….
I suspect very strongly that is some issue with asynchronous things. In fact I´m having a hard time trying to implement async things (such as SELECT, cds.read, etc) inside the entity handlers. Can´t make them work. So I hit this straightforward example but only to find this crazy behavior.
I though it was some problem with my installation (VSCode, nodejs, who knows…), so this time I tried it on the SAP Business Application Studio, but only to get the same results.
Has someone else seen things like these?
You can find my code right here:
https://github.com/ecassaro1/ws1/tree/master/p15
Thanks in advance, Eric
See my answer here: https://answers.sap.com/questions/13102383/cap-and-asyncawait.html?childToView=13099653&answerPublished=true#answer-13099653
Hi DJ,
thanks for the example.
For me with CAP 4.x, i could do the same with new aggregation functions
Hi,
is it possible to assign a default value to the computed field?
Thanks and best regards,
Elias
Hi there,
We have a requirement of getting sum of difference of alphanumeric range columns.
So like we have 2 ranges i.e. from A to and G to K. This can be an alphanumeric range as well. Now we need to sum of difference of Ranges i.e. 7 in above case.
Is there any way to get it.
BR/KR