CAP Filter composition by field

[monokizsolt7]

Hello,

I have a parent entity with an items composition. What is the best way to create a service entity/view, which returns the parents with their items, where the items are filtered by one of their fields?

I'm looking for something like this:

entity Parent as projection on model.Parent { ID, name, items[type_code = '10'] }

I think I could do it with the .on('READ') handler, but it needs to work generally with all odata queries which includes the items, so for example /Parent(x)/items and /Parent?$expand=* should all be filtered.

Thanks, Zsolt

[hjb · Accepted solution]

This is a frequently asked question. Invert your query and ask for items with parents and not parents with items. This way you avoid to navigate along a to-many association but instead follow the to-1 association:

entity Header {
 key id: Integer; 
 name: String; 
 items: composition of many { 
   key pos: Integer; 
   code: String; }};
view V as select from Header.items[code='10'] { up_.id, up_.name, pos};

This is the corresponding SQL statement:

CREATE VIEW V AS SELECT
  up__1.id,
  up__1.name,
FROM (Header_items AS items_0 LEFT JOIN Header AS up__1 ON items_0.up__id = up__1.id)
WHERE items_0.code = '10';

[monokizsolt7 · Accepted solution]

While this wasn't exactly what I was looking for, it helped me find the right way. Now the items are filtered no matter where I look at it from.

entity Parent as projection on model.Parent { ID, name, items }

entity Parent_Items as select from model.Parent_Items where type.code = '10'