on 2022 May 08 2:28 PM
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
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';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.