CAP Custom event handlers & OData queries

[former_member656185]

Hello,

I am currently working on an application using CAP & Node.js. The application is basically a service that aggregates data from existing standard SAP APIs, and a Fiori Elements front-end (List Report & Object Page).

To be able to aggregate the data of the APIs, I use a custom service implementation in Javascript, with the event handlers (before, on, after).

My problem is that I am not able to re-implement the OData V4 $count query (in the custom handlers), that is necessary to have the Fiori Elements list report to work. Apparently the custom handlers "overwrite" the standard $count handler ? Therefore, the List Report issues the following request:

READ Entity {
  '$count': 'true',
  '$select': '...',
  '$skip': '0',
  '$top': '30'
}

But gets the following answer:

  "@odata.context": "$metadata#Entity",
  "@odata.metadataEtag": "...",
  "@odata.count": 0,
  "value": [
    { ... }, { ... }, ...
  ]

Since the @odata.count value is 0, the List Report does not display any data. I did not find any way to set this value in the custom handlers, since it seems that I only have access to the "value" array.

So my question is: Is there a way to set a custom value for the results array and still have the standard OData handlers work ?

My question is specifically for the $count query, but I would also like to avoid re-coding the $filter and $sort logic if possible.

Here is a sample of my current implementation:

srv.on('READ', 'Entity', async (req) => {
	const queryOptions = req._.odataReq.getQueryOptions() || {};

	const [data1, data2, data3] = await Promise.all([
		srv.run(SELECT.from('Entity1')),
		srv.run(SELECT.from('Entity2')),
		srv.run(SELECT.from('Entity3')),
	]);
	const elements = [...data1, ...data2, ...data3]
		.map((el) => ({ Prop1: el.Prop1, Prop2: el.Prop2, Prop3: el.Prop3 }));

	const reqData = req.data;
	if (reqData && reqData.Prop1 && reqData.Prop3) {
		const element = elements.find((el) => el.Prop1 === reqData.Prop1 && el.Prop3 === reqData.Prop3);
		return element;
	}
	const top = queryOptions.$top || flows.length;
	const skip = queryOptions.$skip || 0;
	return elements.slice(skip, (skip + top <= elements.length ? skip + top : elements.length));
});

srv.after('READ', 'Entity', async (results, req) => {
	const queryOptions = req._.odataReq.getQueryOptions();
	if (queryOptions && queryOptions.$expand) {
		if (!Object.keys(req.data).length) {
			req.reject(501, 'Not implemented');
		} else if (results.length) {
			const el = results[0];
			const items = await srv.run(SELECT.from('EntityItems').where({ Entity_Prop1: el.Prop1, Entity_Prop3: el.Prop3 }));
			el.Items = items;
		}
	}
});

srv.on('READ', ['Entity1', 'Entity2', 'Entity3'], async (req) => {
	// Handling with request to external API
}):

Thanks for your help,

Theo

[gregorw]

Hi Theo,

maybe you can share a sample project on GitHub that illustrates the issue?

Best regards
Gregor

[vansyckel]

Hi Theo,

Most query options like $filter and $sort are pushed to the database and not applied to the result set your custom handler returns. Hence, you must deal with those yourself. For $filter it's actually quite simple, as they result in independent where statements that you can pass along to all three remote services (cf. fluent QL API SELECT.from(...).where({...})). For others like $sort, $top, and $skip not so much, as they must be applied to the merged result set.

In the current version if CAP, the query option $count is handled like a read request. In case of an inline count, it triggers a second read inside the app. That is, GET .../Books/$count is a single READ event, GET .../Books?$count=true results in two separate READ events, namely one for fetching the data and one for fetching the count. The on handler for the second event (check req._.odataReq to distinguish the two) needs to return format [{ counted: <count> }], which in turn fills the property @odata.count.

Best,
Sebastian

[former_member656185]

Hello Sebastian,

Thanks for your answer.

Indeed if the external OData entities support the $filter query, the implementation is rather simple, but in my case they do not (they do not have any OData queries implemented).

For the $count query:

• For /$count: I was able to make it workbby checking req._.odataReq._url.path and returning [{ counted: ... }] as you said.
• For ?$count=true however, there is indeed two READ events, but I did not find any distinguishing element in the two event req._.odataReq parameter. Therefore I am not able to know for which request I should return the data, and for which request I should send the counter property. (I was able to simulate the intended behaviour with a counter, but I need a proper way).

Do you know which property I should look at in req._.odataReq ?

Thanks again,

Best,

Théo

[vansyckel]

Hi Theo,

You can check by inspecting the req.query object, which captures the read in CQN notation. One will be the "normal read", one will be the count.

Best,
Sebastian

[former_member656185]

Hello Sebastien,

Thanks a lot, I was able to make it work.

For anyone wondering, here is a sample code to integrate in the srv.on('READ', ...) handler, to handle $count=true query option and .../$count queries:

if (req._.odataReq._url.path.includes('/$count') || req.query.SELECT.columns[0].func === 'count') {
     return [{ counted: elements.length }];
}

(Note that there probably is a cleaner way to do this)

Théo

[daniel_mangold]

Hi,

is this still working with

@sap/cds: 5.7.5

@sap/cds-runtime: 3.3.0 ???

I have the exactly same issue, custom implementation of returning the result data (which basically works fine) but /$count always returns 0. I have tried like return [{ counted: 123 }] but still getting 0 back, always.

Best,

Daniel

[daniel_mangold]

It works, but it is actually

return [{ _counted_: 123 }]

[former_member160153]

daniel.mangold

Hi Daniel,

I put following code in my custom handler, but it doesn't work and returns an empty array in values section. Do you have any idea? Thanks

srv.on("READ", "Records", async (req, next) => { 

  var results = [];

  //.....ignored code

  if (req._.odataReq._url.path.includes('$count') || req.query.SELECT.columns[0].func === 'count') 
  {
    return [{ _counted_: results.length }]; 
  } 
  return reuslts;
}

[hatrigt]

I have implemented the same kind of scenario. Wherein, In the service implementation, I read the data on SRV.ON event. However, in the List report, I have search and filter on some fields. They are not working. How can I implement search and filter manually in the custom handlers?

[Girish_Kumar_Gu]

Hi hatrigt

I have a similar requirement. Could you find any approach to solve your problem?

TIA

[Swetha__Balusamy]

I am also facing the same issue. Please let me know if you have found the solution

[Swetha__Balusamy]

Found a solution to this issue. We need to add '$count' as part of the result sent from the custom event handler.

[Swetha__Balusamy]

Found a solution to this issue. We need to add '$count' as part of the result sent from the custom event handler.
results['$count'] = response["d"].__count;