SAP Cloud Platform Backend service: Tutorial [9]: CDS : how to use select statement

This is another tutorial, part of the series of tutorials explaining the usage of
SAP Cloud Platform Backend service in detail.

In one of the previous tutorials we’ve learned to move the service definition into a separate section.
And which additional benefits this has.

In this tutorial we’ll focus again on this topic.
In the service definition section you can add select statements
This is quite powerful, I have to say
sounds good

Recap: the service definition section

Until now, we’ve done something like this:

entity BusinessPartnerEntity{
    key id: Integer;
    name : String;
}

service BusinessPartnerService {
    entity Customers as projection on BusinessPartnerEntity;
}

Define entities in the data model,
In the service model, define entities to be exposed, as projection on …

 

Example 1: compare projection and  select statement

Now, instead of projection, we’ll use a select statement.

First, let’s compare 2 identical expressions:

entity BusinessPartnerEntity{
    key id: Integer;
    name : String;
    country : String;
}

service BusinessPartnerService {

    // we used this up to now
    entity Customers as projection on BusinessPartnerEntity {
                                        id,
                                        name,
                                        country
                                    };

    // the same, with select statement
    entity Customers2  as select from BusinessPartnerEntity {
                                        id, 
                                        name, 
                                        country
                                    }; 
}

 

If you create an API based on this model, you’ll see that the OData service has 2 EntityTypes which are exactly the same.
If you create some data and fetch the collection using both entity sets, then you’ll receive exactly the same payload.

Note:
You can use wildcard:

entity Customers as select from BusinessPartnerEntity {*} ;

Now let’s try a different example.

 

Example 2 : select statement with where clause

    . . .

    entity Customers as projection on BusinessPartnerEntity;

    entity CustomersUSA  as select from BusinessPartnerEntity {
                                        id, 
                                        name, 
                                        country
                                    } 
                                    where country  = 'USA'  ;

In this service definition, we expose an entity and a second entity, using projection and select statements

In both cases the data is fetched from the same table.
Moreover, the select statement allows to choose the desired columns
And on top of this, it is possible to specify a filter (where).
Like this, we’re able to do very fine-granular modelling.

This means that in this example, the returned collection is not the same for “Customers” and “CustomersUSA”
In case of “CustomersUSA”, there’s a filter applied directly on the database

Note:
Sometimes when using Backend service, I feel a bit unsatisfied, because I cannot see what’s actually happening under the hood. Like looking into the database. Reading logs, etc
Means, there are disadvantages at serverless computing
Do you feel the same?
I feel tired
OK, forget it

Example 3: one more example for select

See below a full example:

We have the normal collection of all customers.
In addition, we have a collection where we get only the customers located in our home country (in this case just as example, it is the USA)
A third collection gives an example of wildcard and fetching only entries with null value (e.g. for maintenance, or whatever use case)

entity BusinessPartnerEntity{
    key id: Integer;
    name : String;
    country : String;
}

service BusinessPartnerService {

    // projection as we used before
    entity Customers as projection on BusinessPartnerEntity;

    // example for select statement in service definition section
    entity HomeCustomers  as select from BusinessPartnerEntity {
                                        id, 
                                        name, 
                                        country
                                    } 
                                    where country  = 'USA'  ;

    // another example
    entity HomelessCustomers  as select from BusinessPartnerEntity {*} 
                                    where country  is null; 
}

 

Try it:

After API generation, create some sample data.

Use the (generic) “Customers” collection to create several entries.
For instance:

{
“id”: 1,
“name”: “customer1”,
“country”: “USA”
}

Some entries with such property value:
“country” : “IND”

And some with no country at all, e.g.:

{
“id”: 4,
“name”: “customer4”
}

 

After creating some sample data, invoke the 2 collections, which have a select statement under the hood.
I’d like to see what’s under the hood…
I feel so tired…

 

But first to see the full payload, we use the (generic) “Customers” collection:

https://backend-service…/…/<SERVICE>/Customers

The result looks like this:

Then the USA-based “HomeCustomers”

https://backend-service…/…/<SERVICE>/HomeCustomers

 

And finally the collection of invalid entries:

https://backend-service…/…/<SERVICE>/HomelessCustomers

What we’ve seen here is that we have 3 entity sets operating on the same database table, and each entity set displays a different list of entries.

The last example looks strange

Yes, we explicitly list entries to always see one null property.
Why do I have to see the null if I’ve anyways specified it?
Yes, this can be enhanced.
We can exclude those properties which we don’t need to see.
How?
Just keep quiet and go ahead to the next example.

Example 4: select and excluding

In this example
we add an “excluding” to the select, just for showing that it is possible,
and we add another filter,
and we add an alias…
Why?
…just for showing that it is possible

entity BusinessPartnerEntity{
    key id: Integer;
    name : String;
    country : String;
    createdAt : Timestamp;
}

service BusinessPartnerService {

    // using projection
    entity Customers as projection on BusinessPartnerEntity;
    
    // using select with where
    entity HomelessCustomers  as select from BusinessPartnerEntity {
                                        id, 
                                        name, 
                                        country as homeCountry
                                    } excluding { 
                                        createdAt 
                                    }
                                    where country  is null  and name is not null;
}

The alias works same way like we already know from the projection.
Also the excluding is not a  surprise.
And as you can see, complex filter statements are possible.
That is not complex
Would be possible

Try it:

After API generation, create some sample data.

{
“id”: 1,
“name”: “customer1”,
“country”: “USA”
}

{
“id”: 2,
“name”: “Customer2”
}

{
“id”: 3
}

 

Afterwards see the “Customers” entity set with all 3 entries and compare to “HomelessCustomers” entity set with only one entry.
Furthermore, the exclude works and the alias as well:

 

 

Example 5: select between

Just an example for another select statement

entity BusinessPartnerEntity{
    key id: Integer;
    name : String;
    country : String;
    createdAt : Timestamp;
}

service BusinessPartnerService {
    entity Customers as projection on BusinessPartnerEntity;   
    entity FirstTenCustomers as select from BusinessPartnerEntity {*} 
                                      where id between 0 and 10;                                     
}

You can test it as usual, create entries with id numbers as 1 and 2 and 11
When invoking the Customers collection, all 3 entries will be there, when invoking the FirstTenCustomers collection, then only 2 are displayed

 

Example 6: select like

Short look how this kind of filter works.
Use the keyword “like” and a string with placeholder.
For example:

entity CustomersUnknown as select from BusinessPartnerEntity {*} 
                              where country like '%know%';

For testing, create some entries with the following payload:

{
“id”: 1,
“name”: “customer1”,
“country”: “unknown”
}
{
“id”: 2,
“name”: “customer2”,
“country”: “don’t know”
}
{
“id”: 3,
“name”: “customer3”,
“country”: “USA”
}
{
“id”: 4,
“name”: “customer4”,
“country”: “not now”
}

When testing the entity sets, you’ll see that the “CustomersUnknown” contain only the first 2 entries.
The entries 3 and 4 don’t match the “like”-expression.

Example 7:   shortcut      is not      !=

This example shows how the where clause can be shortened.
Also, it shows the difference between is not and !=

entity CustomersNotNull 
       as select from BusinessPartnerEntity[country is not null] {*} ; 

entity CustomersNotUSA 
       as select from BusinessPartnerEntity[country != 'USA'] {*} ; 

 

{
“id”: 1,
“name”: “customer1”,
“country”: “USA”
}

{
“id”: 2,
“name”: “customer2”,
“country”: “GER”
}

{
“id”: 3,
“name”: “customer3”
}

Result:
The “CustomersNotNull” collection will display the first 2 entries
The “CustomersNotUSA” collection will display only the second entry

Example 8 : why use select statements?

Imagine you create a service and you want to have fine granular restrictions on the users who are allowed to use the service.
E.g., the ADMIN user is usually not allowed to invoke the services and view customer data

However, what you can do is:
Specify one entity which has a filtered view on the database, renamed properties, excluded sensitive properties
The data which can be viewed could be for instance the administrative data like “createdAt”, “lastModifiedAt”, and so on
Once you’ve created the API, you can create a Role based on APIAccess and configure it with this special entity, and assign this role to the user

Example 9: some FAQ

No example here, only these FAQs:

Why do I need select/where ? I can do filter query in OData

Yes, a select statement with where clause in the CDS model is like a query with filter in the odata service.
We can achieve exactly the same effect, like the example above, if we use the below filter, instead of a where

<SERVICE>/<Customers>?$filter=country eq 'USA'

Question not answered. Why should I do the select/where ?

In case of select/where in CDS, the filter is built directly into the database (almost).
As a consequence: only the required data is fetched from the database.

In case of $filter query with the OData service, the filter is done in the java layer (if java is used as language, better phrase it as framework layer). The generic implementation translates the OData filter statement into a CDS query
As a consequence: only the required data is sent via the network

In case of no select and no filter, the full data would be fetched from database and sent via the network to the web application, and the filtering would be done there, in the UI layer

Summarizing:
The first option (select) has the best performance, but of course it is a hard-coded filter and can only be applied to use cases which can be foreseen.
The second option ($filter) is fully flexible, with good performance, because no irrelevant data is sent via the network (important for mobile consumer-applications)
The third option is obviously discouraged.

 

Summary

In this tutorial, we’ve done a deep dive into the select statement.
This statement can be used in the service definition, to expose entities with restrictions on the exposed data.

 

Links