Skip to Content
Technical Articles
Author's profile photo DJ Adams

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

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Pierre Dominique
      Pierre Dominique

      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

      Author's profile photo DJ Adams
      DJ Adams
      Blog Post Author

      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 🙂

      Author's profile photo Pierre Dominique
      Pierre Dominique

      I found a way to do it using CDS (just because I wanted to know if this was possible :-)):

      service CatalogService {
      
      	entity Books as projection on my.Books;
      	entity Authors as select from my.Authors, my.Books {
      		key Authors.ID,
          name,
      		@Core.Computed
      	  count(Books.ID) as numberOfBooks: Integer
      	} where Authors.books.ID = Books.ID
          group by Authors.ID;
      }

      cds compile .\srv\cat-service.cds –to sql :

      CREATE VIEW CatalogService_Authors AS SELECT
        Authors_0.ID,
        Authors_0.name,
        COUNT(Books_1.ID) AS numberOfBooks
      FROM ((my_bookshop_Authors AS Authors_0 CROSS JOIN my_bookshop_Books AS Books_1) LEFT JOIN my_bookshop_Books AS Books_2 ON (Books_2.author_ID = Authors_0.ID))
      WHERE Books_2.ID = Books_1.ID
      GROUP BY Authors_0.ID;
      Author's profile photo DJ Adams
      DJ Adams
      Blog Post Author

      Nice work, Pierre! Hopefully this will also be useful to others.

      Author's profile photo javier Rodriguez Prieto
      javier Rodriguez Prieto

      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

      using my.location as my from '../db/data-model';
      
      
      service CatalogService  {
          entity Zstreets_poland as projection on my.ZStreets_poland;
      }
      
      
      service CustomService @(requires:'ztest-p1941544866trial.Display')  {
          entity Zstreets_poland as projection on my.ZStreets_poland;
      }
      
      service CustomServicedentro {
           @(readonly, requires:'testing-p1941544866trial!t20471.Display')
          entity Zstreets_poland as projection on my.ZStreets_poland;
      }
      
      service CustomServicedentro2 {
           @(readonly, requires:'Display')
          entity Zstreets_poland as projection on my.ZStreets_poland;
      }

      My cdsrc.json:

          "auth": {
              "passport":{
                  "strategy": "JWT"
              }
          }
      
      }

      And my app protected with route:

      {
          "welcomeFile": "app/index.html",
          "authenticationMethod": "route",
          "logout": {
            "logoutEndpoint": "/do/logout"
          },
          "routes": [
              {
                "source": "^/app/(.*)$",
                "target": "$1",
                "localDir": "/resources",
                "authenticationType": "xsuaa"
              },
              {
                "source": "^/srv/(.*)$",
                "destination": "srv_api"
              }
          ]
      }

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

      {
          "xsappname": "testing",
          "tenant-mode": "dedicated",
          "scopes": [
            {
              "name": "$XSAPPNAME.Display",
              "description": "display"
            }
          ],
          "role-templates": [
            {
              "name": "App_ztest_service",
              "description": "Read books",
              "scope-references": [
                "$XSAPPNAME.Display"
              ]
            }
          ]
        }

      And my yaml:

       - name: ztest-app
          type: html5
          path: app
          parameters:
            disk-quota: 256M
            memory: 256M
          requires:
            - name: ztest-dest
            - name: ztest-uaa
            - name: srv_api
              group: destinations
              properties:
                name: srv_api
                url: '~{url}'
                forwardAuthToken: true
                strictSSL: false
      resources:
        - name: ztest-uaa
          type: org.cloudfoundry.managed-service
          parameters:
            path: ./xs-security.json
            service-plan: application
            service: xsuaa
        - name: ztest-dest
          type: org.cloudfoundry.managed-service
          parameters:
            service-plan: lite
            service: destination

      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

      Author's profile photo DJ Adams
      DJ Adams
      Blog Post Author

      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

      Author's profile photo Sergio Congia
      Sergio Congia

      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

      namespace com.or.assistant;
      
      using { managed, temporal, cuid } from '@sap/cds/common';
      
      type Role : String(10);
      type RoleName : String(255);
      
      entity Persons: cuid, managed {
          phoneNumber		: String(20);
          firstName		: String(200);
          lastName		: String(200);
          initials        	: String(10);
          title		: String(4);
          dateOfBirth		: Date;
          idNumber		: String(50);  
          eMail		: String(100);
          Roles		: Composition of many Person_Role on Roles.parent=$self;
          gender              : String(1) 
            @( title: 'Gender', ) enum {
              male    = 'M' @( title: 'Male');
              female  = 'F' @( title: 'Female');
              other   = 'O' @( title: 'Other');
          };
      };
      
      entity Person_Role : cuid{
          parent	        : Association to Persons;
          role		: Role;
      };
      
      entity Roles : managed{
          key ID          : Role        @( title: 'Role ID', ); 
          roleName        : RoleName    @( title: 'Role Name', );
          description     : String      @( title: 'Description', );
      
          category        : String(2) 
            @( title: 'Category', ) enum {
              medDoctor   = 'MD' @( title: 'Medical Doctor');
              patient     = 'PA' @( title: 'Patient');
              nurse       = 'NU' @( title: 'Nursing');
          };        
      };

      srv/admin-service.cds

      service AdminService  @(path:'/admin') {
          entity Persons as projection on db.Persons;
          annotate Persons with @odata.draft.enabled;
          
          //------- auto-exposed -------- Copied from Gregor's bookshop-demo
          //entity Person_Role as projection on db.Person_Role;
          //> these shall be removed but this would break the Fiori UI
      
          // qmacro approach
          // Results in error "SQLITE_ERROR: no such column: roleName"
          // Does not conflict with auto-exposure, but requires logic in Srv to insert as 
          entity Person_Role as select from db.Person_Role{
              *,
              null as roleName : db.RoleName
          };
      
          // Pierre Dominique approach
          // Note: Conflicts with auto-exposed from @odata.draft.enabled
          //
          // entity Person_Role as select from db.Person_Role, db.Roles {
          //     Person_Role.ID,
          //     Person_Role.parent,
          //     Person_Role.role,
          //     Roles.roleName
          // } where Person_Role.ID = Roles.ID;
      
          entity Roles as projection on db.Roles;
      }
      Author's profile photo DJ Adams
      DJ Adams
      Blog Post Author

      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!

      Author's profile photo Sergio Congia
      Sergio Congia

      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.

      Author's profile photo sergio congia
      sergio congia

      Hey qmacro

      Is there a similar approach to define/return an association/entityset at service level?

      S

      Author's profile photo Eric Cassaro
      Eric Cassaro

      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:

      {
      @odata.context: "$metadata#Authors",
      value: [
      {
      ID: 101,
      name: "Emily Brontë",
      numberOfBooks: 1
      },
      {
      ID: 107,
      name: "Charlote Brontë",
      numberOfBooks: null
      },
      {
      ID: 150,
      name: "Edgar Allen Poe",
      numberOfBooks: 2
      },
      {
      ID: 170,
      name: "Richard Carpenter",
      numberOfBooks: null
      }
      ]
      }

      (hit refresh…)

      {
      @odata.context: "$metadata#Authors",
      value: [
      {
      ID: 101,
      name: "Emily Brontë",
      numberOfBooks: null
      },
      {
      ID: 107,
      name: "Charlote Brontë",
      numberOfBooks: null
      },
      {
      ID: 150,
      name: "Edgar Allen Poe",
      numberOfBooks: null
      },
      {
      ID: 170,
      name: "Richard Carpenter",
      numberOfBooks: null
      }
      ]
      }

      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

      Author's profile photo Sebastian Esch
      Sebastian Esch

      See my answer here: https://answers.sap.com/questions/13102383/cap-and-asyncawait.html?childToView=13099653&answerPublished=true#answer-13099653

      Author's profile photo Holger Schäfer
      Holger Schäfer

      Hi DJ,

      thanks for the example.

      For me with CAP 4.x, i could do the same with new aggregation functions

      /ShopOrders$apply=groupby((Status_Status),aggregate($count as Count))&$expand=Status
      but this does not worked for the v2 adapter, so i fallback to your solution with a custom view, because my CAP Mockservice needs to be adopted to RAP by another dev team.
      Best Regards
      Holger
      Author's profile photo Elias Müller
      Elias Müller

      Hi,

      is it possible to assign a default value to the computed field?

      Thanks and best regards,

      Elias

      Author's profile photo Krishan Raheja
      Krishan Raheja

      Hi there,

       

      We have a requirement of getting sum of difference of alphanumeric range columns.

       

      Range From Range To Difference
      A D 3
      G K 4
      Sum 7

       

      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