Aggregations in CAP services

[jthuijls]

Hello,

Trying to write an aggregation on a CAP service. Imagine the following schema:

entity Master : cuid {
  description: String(100);
  details: Composition of many Details on details.master = $self;
}

entity Details: cuid {
  master: Association to Master;
  value: Integer; 
}

And the following service:

service TestService @(path: '/test') {
  entity Master as projection on t.Master;
  entity Details as projection on t.Details;
  entity Aggregation as select from t.Master left outer join t.Details on Master.ID = Details.master.ID {
    Master.ID,
    sum(Details.value) as value: Integer
  } group by Master.ID;
}

Testing service `/test/Master?$expand=details` works exactly as expected and outputs the JSON I was looking for.

Testing service `/test/Aggregation` results in:

[2020-08-13T03:47:40.148Z | ERROR | 1774875]: SQLITE_ERROR: near "LIMIT": syntax error
[2020-08-13T03:47:40.149Z | ERROR | 1774875]: Error stacktrace: Error: SQLITE_ERROR: near "LIMIT": syntax error

What is the correct way of adding adding a sum or count or whatever to a service? Are there different or better ways of doing this?

[david_kunz2 · Accepted solution]

Hi Jorg,

I think the problem here is, that there is no `key` specified (leading to a different bug - will be fixed in the next release). Can you try to write the following:

  entity Aggregation as select from t.Master left outer join t.Details on Master.ID = Details.master.ID {
    key Master.ID,
    sum(Details.value) as value: Integer
  } group by Master.ID;

Thanks and best regards,
David

[jthuijls · Accepted solution]

Thanks David, missing `key` was the right answer. Follow-up question: can I use such an entity as an association to another entity?

Edit: figured it out. I've moved the entity to the schema file, where it's generated as a view and can be used in other entities