Counting associated entities using CDS

[pierre_dominique2]

Hi,

Is there a way to count the number of associated entities using CDS? Let's say I'd like to display the number of books written by a particular author:

  entity Books {
    key ID : Integer;
    title  : localized String;
    author : Association to Authors;
  }

  entity Authors {
    key ID : Integer;
    name   : String;
    books  : Association to many Books on books.author = $self;
    @Core.Computed
    numberOfBooks : Integer;
  }

I tried something like count(books) as numberOfBooks but it's not supported 🙂

Cheers,

Pierre

[qmacro · Accepted solution]

I wrote up one possible answer in the form of a blog post:

https://blogs.sap.com/2019/08/21/computed-field-example-in-cap/

Hope that helps!

[pierre_dominique2 · Accepted solution]

Thank you for this very detailed answer DJ!

Pierre

[johannesvogel · Accepted solution]

Hi Pierre,

it is possible to define the view on CDS level to push down the count calculation to the database. You just need to do a minimal adaption of the view definition in DJ's example:

service CatalogService {
    entity Books as projection on my.Books;
    entity Authors as select from my.Authors {
	*,
	@Core.Computed
	count(1) as numberOfBooks: Integer
	};
}

Hope this helps,

Johannes

[pierre_dominique2 · Accepted solution]

Hi johannesvogel,

This doesn't really work as intended, please see my solution below.

Cheers,

Pierre

[pierre_dominique2]

Hi Johannes,

This doesn't really work as intended because then numberOfBooks contains the total number of authors and the response only contains 1 author:

{
	"@odata.context": "$metadata#Authors",
	"@odata.metadataEtag": "W/\"8q5jjLD6vJ0ARrjnkajTONXIn38vpa1wxoXucua4kzU=\"",
	"value": [{
		"ID": 101,
		"name": "Emily Brontë",
		"numberOfBooks": 4
	}]
}<br>

This is what I get with DJ's solution:

{
	"@odata.context": "$metadata#Authors",
	"@odata.metadataEtag": "W/\"8q5jjLD6vJ0ARrjnkajTONXIn38vpa1wxoXucua4kzU=\"",
	"value": [{
			"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
		}
	]
}<br>

EDIT: here's a solution, using count at the CDS level:

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;

Cheers,

Pierre

[johannesvogel]

Hi Pierre,

I completely missed the point, that the associated Books should be counted.

Sorry for that!

Seems like you found the solution anyway (y)

[gregorw]

I would think you should watch this episode of Hands-on SAP dev with dj.adams.sap where he showed how to create a calculated value.

[qmacro]

Ha, nice one Gregor, thanks, yes, I'd recommend this. But it's still on Twitch (I need to annotate it and get it moved to YT) - can you access that now, Pierre?

[pierre_dominique2]

Thanks Gregor, I'll watch this episode. Now I'm curious and wondering if DJ's solution involves hooks/handlers.

dj.adams.sap : no but I'll watch the replay at home or wait for the annotated version on YT.

[markteichmann]

In the OData standard this case is described here: https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Example:

https://services.odata.org/OData/OData.svc/Categories(1)/Products/$count

• Identifies the number of Product Entries associated with Category 1.
• Is described by the Navigation Property named "Products" on the "Category" Entity Type in the service metadata document.

This would be ../Authors(3)/books/$count then. Would be interesting to know if this works out of the box or if you have to do some coding in CDS or if it is not implemented yet in CDS.

[pierre_dominique2]

And this is what I use in another (freestyle UI5) application but I was wondering if there's a way to do it at the CDS level because I want to use this service in a Fiori Elements app and I'd like to avoid having to create an extension.

$count works just fine out of the box with CAP Java.

[p244500]

Hi,

kindly check bellow link

Why counting distinct values in ABAP CDS isn’t as easy as you think

[pierre_dominique2]

Thanks but I've already seen this blog and the topic is a bit different (ABAP CDS vs CAP CDS).

Pierre