cancel
Showing results for 
Search instead for 
Did you mean: 

Counting associated entities using CDS

pierre_dominique2
Contributor
3,804

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

Accepted Solutions (1)

Accepted Solutions (1)

qmacro
Developer Advocate
Developer Advocate

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
Contributor

Thank you for this very detailed answer DJ!

Pierre

johannesvogel
Advisor
Advisor

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
Contributor
0 Kudos

Hi johannesvogel,

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

Cheers,

Pierre

Answers (4)

Answers (4)

pierre_dominique2
Contributor

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
Advisor
Advisor
0 Kudos

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
Active Contributor

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
Developer Advocate
Developer Advocate
0 Kudos

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
Contributor

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
Product and Topic Expert
Product and Topic Expert

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
Contributor
0 Kudos

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
Active Contributor
0 Kudos
pierre_dominique2
Contributor
0 Kudos

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

Pierre