In the first part of my blog post, I've shown how you can get familiar with cds.ql and how the syntax works for you. If you are not very familiar with cds.ql so far, I'd recommend to read the documentation (
https://cap.cloud.sap/docs/node.js/cds-ql) and visit the first part (
https://blogs.sap.com/2022/06/21/getting-started-with-cds.ql-in-nodejs-for-cap-101-on-how-to-query-d...).
This blog post is dedicated to understand the true value of projection functions, and what it means in term of constructing and executing queries with joins (or rather 1:1 or 1:n relations).
Prerequisites
1. Make sure you have a schema.cds or data-model.cds
For this blog post you should have already your data model defined. There are various documentations pages and blog posts about CAP’s capabilities of data modelling. In the following, we will use this sample schema.cds
namespace sap.samples;
entity Receiver : cuid {
name : String(128);
countryOfOrigin : Association to one Country;
noOfReceipts : Integer default 0;
phoneNumber : String(32);
receivedObjects : Composition of many ReceivedObjects on receivedObjects.receiver = $self;
}
entity Country {
key code : String(2);
countryName : String(128);
}
entity Sender : cuid {
name : String(128);
countryOfOrigin : Association to one Country;
hasPrimeShipping : Boolean default false;
defaultPackagePriority : String(1) enum {
A;
B;
} default 'B';
typeOfSender : String(32) enum {
daily;
everyOtherDay;
weekly;
monthly;
quarterly;
}
}
entity ReceivedObjects : cuid {
receiver : Association to one Reciever;
sender : Association to one Sender;
objectName : String(128);
deliveryDate : Timestamp;
}
This is an enhancement of the data model of the first blog posts. Note: The new entity ReceivedObjects and the 1:n relation from receiver to its objects.
2. Prepared a service handler
I'll fast forward this step, by creating a function, where I want all object names comma-separated by receiver ID in a string format.
srv.on("objectsByReceiver", async (req) => {
const { name } = req.data;
});
If you have difficulties to understand how this construct works, please revisit the documentation and first tutorials.
Understand the purpose of projection functions
Before I deep-dive into the functionalities of projection functions, let's make sure, why they exist and which problem they aim to solve.
For querying data for the database, cds.ql delivers you a rich set of functionalities to apply database selections and projections. If you need to read an entire table or single row, the where-queries allow you to restrict your search.
This works fine until, you are forced to retrieve data from multiple tables or even 1:n associations/compositions. The issue you face, is that you must ungroup JOINs into single queries and for retrieving 1:n relationships, you even end up in a ton of queries. To visualise this issue, the following code snippet(s) represent different options to solve our function problem:
Sample: First get receiver ID by name and then get received objects
srv.on("objectsByReceiver", async (req) => {
const { name } = req.data;
const receiver = await SELECT.one.from(Receiver).where({ name });
if (receiver) {
const receivedObjects = await SELECT.from(ReceivedObjects).columns("objectName").where({ receiver_ID: receiver.ID });
const aObjectNames = receivedObjects.map(item => item.objectName).flat();
return aObjectNames.join(", ")
}
return "";
});
While you might think, this seems fairly okay, then consider a function where you do not have one receiver, but many, and you must join the object names.
Projection functions are a helpful enhancement to query a table incl. its associations and compositions into one cds.ql query and let the CAP framework do the magic for you. The following sample, shows you, how a projection function could solve the previous task:
srv.on("objectsByReceiver", async (req) => {
const { name } = req.data;
const receivedObjects = await SELECT.one.from(Receiver, r => {
r.receivedObjects(
ro => {
ro.objectName
}
)
}).where({ name });
return receivedObjects?.receivedObjects?.map(item => item.objectName)?.flat()?.join(", ") ?? "";
});
You see, you end up with a much smoother and readable code fragment (besides my love of optional chaining expressions ;-))
Understand the syntax of projection functions
Projection functions (
https://pages.github.tools.sap/cap/docs/node.js/cds-ql#columns) enhance your cds.ql statement in the .columns or .from method. With a projection function, you define your target structure of your SQL query.
In the previous example, you use "r" as a representation for a receiver entry (arrow-function parameter). You are not interested in retrieving any information of the receiver itself, but its received objects. Hence you use the navigation path "receivedObjects" from your Receiver entity.
Each receivedObject is represented by "ro". Again, you are not interested in all fields, so you state, you explicitly only want the object name.
The return value of this statement, would be a JSON like this:
{
receivedObjects: [
{
objectName: "Banana"
},
{
objectName: "Palm Tree"
}
]
}
To get in deeper, I've prepared a collection of sample queries for you
SELECT queries for 1:1 relationships
1:1 relationships are more or less an JOIN between two tables. Without a projection function, you would need two separate queries you send to the db and await. With a projection function, you can do this in one.
Let's imagine, you have a ReceivedObject ID and you want its name the receiver's name:
const oResult = await SELECT.one.from(ReceivedObjects, ro => {
ro.objectName;
ro.receiver (
r => {
r.name
}
)
}).where({ ID: roID });
What we have done, is to query the ReceivedObjects table and expanded it with its receiver. A JSON output could look as following:
{
objectName: "Banana"
receiver: {
name: "John Doe"
}
}
Using wildcards in projection functions
Sometimes, it may occur, you want to get all information of a receiver, so you don't want to write all properties into your query. Hence, we can use the wildcard option to get the entire receiver object, by using this linter-friendly syntax:
const oResult = await SELECT.one.from(ReceivedObjects, ro => {
ro.objectName;
ro.receiver (
r => {
r('*')
}
)
}).where({ ID: roID });
This would give you following output:
{
objectName: "Banana"
receiver: {
name: "John Doe",
countryOfOrigin_ID: "CH",
noOfReceipts: 5
...
}
}
Using multiple navigations in projection functions
Projections functions are not limited on just one JOIN operation. Let's imagine, you don't want to only get information about your receiver, but maybe your sender's name as well. So we do the following:
const oResult = await SELECT.one.from(ReceivedObjects, ro => {
ro.objectName;
ro.receiver (
r => {
r('*')
}
),
ro.sender (
s => {
s.name
}
)
}).where({ ID: roID });
Using multi-layer navigation in projection functions
Sometimes, you even come across, that you would need a left join from one entity of a left join of another entity. The great thing is, it's a simple as that, since each new arrow-style callback function (like r = r('*)), you defined, itself allows you of selecting properties or navigating forward.
const oResult = await SELECT.one.from(ReceivedObjects, ro => {
ro.objectName;
ro.receiver (
r => {
r('*');
r.countryOfOrigin ( coc => {
coc.name;
})
}
),
}).where({ ID: roID });
Be aware, you do not end up in a flat hierarchy of a representation of your data, but in a nested object format, e.g. oResult.receiver.countryOfOrigin.name.
Using aliases in projection functions
Nothing easier than that. Same sample like above:
const oResult = await SELECT.one.from(ReceivedObjects, ro => {
ro.objectName;
ro.receiver (
r => {
r('*');
r.countryOfOrigin ( coc => {
coc.name`as countryName`;
})
}
),
}).where({ ID: roID });
SELECT queries for 1:n relationships
The great benefit is, you don't need to do anything different than above. Did you see that I've already introduced an 1:n sample to you?
The syntax remains the same, but you do get an array of objects rather an object in your nested data representation (which kinda makes sense, isn't it?)
const receivedObjects = await SELECT.one.from(Receiver, r => {
r.receivedObjects(
ro => {
ro.objectName
}
)
}).where({ name });
Infix filters on 1:n in projection functions
You may think, this would already drastically simplify my coding and this would create a much more robust application, but you may come across the need, to filter on some of your 1:n relations.
It's fairly easy, if you e.g. want to know the delivery dates of the bananas:
const receivedObjects = await SELECT.one.from(Receiver, r => {
r.receivedObjects(
ro`[objectName='Banana']` => {
ro.deliveryDate
}
)
}).where({ name });
Infix filters are a great way to keep using projection function and reduce the output variable to the data you actually need.
Filtering based on association to one values
The same feature can be applied more or less, reversed, by filtering for something of a nested property. You can do this by using a tagged literal as where clause, and move via dot-notation across your associations (note, works only for 1:1):
const oResult = await SELECT.one.from(ReceivedObjects, ro => {
ro.objectName;
ro.receiver (
r => {
r('*');
}
),
}).where`sender.name = 'John'`;
Wrap-Up
You are now a pro in using projection function. If not, just try it, it's easier then you'd think.
Whenever you use projection functions, I'd recommend you the following:
- Limit the amount of properties you need to a minimum
- Avoid an overuse of wildcards, since you could retrieve a heavy set of data
- Make sure, you use aliases only when needed
- Always remember, you get a nested data presentation and not a flat hierarchy
- Whenever you navigate through a nested structure, ensure you use fallbacks or an optional chaining notation like "?.". You could get also no receiver, when you don't have an entry in db
- Use useful abbrevations for your projection callback function parameters, since they shorten your code massively. My rule of thumb, use the first letter of each word of your entity
- You use the navigation name of the entity not the entity name from the CDS to navigate (therefore small written, not capital in my samples). Make sure, you name them appropriately and make your fellows clear, when you have a 1:1 or 1:n relationship by giving correctly singular or plural names
- Last but not least: Use them! They are great. But only, when you would have multiple queries otherwise semantically connected to each other.
I hope you enjoyed this blog post and got a deeper understanding how to use projection functions.
