- SAP Managed Tags:
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
Until now, we've done something like this:
Define entities in the data model,
In the service model, define entities to be exposed, as projection on ...
Now, instead of projection, we’ll use a select statement.
First, let’s compare 2 identical expressions:
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:
Now let’s try a different example.
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
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)
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.
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
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:
Just an example for another select statement
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
Short look how this kind of filter works.
Use the keyword “like” and a string with placeholder.
For example:
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.
This example shows how the where clause can be shortened.
Also, it shows the difference between is not and !=
{
"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
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
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
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.
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
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
3 Comments
