How to select entities where query involves rel navigation + summing values?
I'm having trouble trying to figure this out, would appreciate any pointers. I have the following Domain Model: Entity Category(name: String, total: Decimal) Entity Expense(description: String, amount: Decimal) Relationship Expense_Category (Each Category includes many Expenses / Each Expense is a member of exactly one Category) I'm trying to create a microflow that selects categories where the total expense for the category is greater than some threshold (i.e. threshold is a parameter to the microflow). At first it looked easy. Category.total is a calculated attribute that sums the amount for each associated Expense. So I tried the XPath query [total > $threshold] However that doesn't work, because calculated attributes aren't allowed in XPath queries (pity!). Next, I tried a flow as follows: Select all categories from the database Feed into a filter action Try to filter on total > threshold However, filters only seem to support equality testing - not inequality. I'm a bit stumped. I'm sure this is a common pattern; is there a preferred way to solve it? EDIT Thanks Ronald, Mike for the answers. The general suggestion seems to be: use a stored attribute rather than a calculated one. As well as enabling the simple query solution, it provides scope for how best to optimise the calculation (i.e. when / how often it's done). It does raise a follow on question: how to prevent other flows from updating the attribute independently? Semantically, the value has to represent the total. If some other flow can write to it independently then those semantics are lost. So: is there a way to (a) store the attribute, but (b) limit write access to just one microflow? (Maybe through security permissions??). Thanks.
Scott - In answer to your follow on question, you can do the following:
create an access rule on the entity such that only one role (for instance Administrator) has write access to that attribute
create a separate access rule that grants read access for that attribute to all other roles (that should have it)
ensure that your scheduled event microflow only has Administrator in allowed roles
set apply entity access to true on the scheduled event microflow
This will ensure that only those users with Administrator access can update the value, and if the scheduled event microflow is not exposed via the user interface, no one will have the capability to update this via a browser (or mobile app).
What this won't do is ensure that another developer won't write a microflow that updates the value, or that the field won't be included on a page where it can be updated. The modeler does not have this kind of security checking during development AFAIK, i.e. there are not user roles for developers. What you can do is during acceptance testing and code review for new versions, check usages of the attribute and see if there are other microflows that update it (in your domain model, right mouse click the attribute and select 'Find Changes in Microflows' from the popup menu to see what microflows change this attribute, next select 'Find Usages' in the same menu to see what pages the attribute is used on)
The quick workaround would be to create a list, iterate over all the objects and add them to the list when total > threshold. If you have very many objects you should do this in a batch.
But is there a reason not to change the calculated attribute in a normal one?
By using the after commit event or after delete event it does not matter what other part of the application does with the object. That will also trigger the aftter commit event and thus update the value.
I am putting this in an answer because it is too long to fit in a comment.
Calculated attributes are calculated every time an object is retrieved, even if the calculated attribute is not displayed or used on a page. As a consequence, calculated attributes can have a significant impact on performance and use lots of server resources. In the case you describe, it seems like there may be a large number of expense objects for each category (if this is an expense report model), so this calculated attribute will wind up being expensive. For instance, lets say you have 50,000 expense records. Each time you retrieve or display a category object, those 50,000 records will be retrieved and summarized. If its a larger application, you'll have that activity taking place over a larger number of records. If my assumption of a large number of expense records is correct, this attribute is not a good candidate to be a calculated attribute.
I would suggest the following alternative approaches, both involve changing the calculated attribute to a stored attribute:
If it is important that the totals for each expense category be updated real time (i.e. as expenses are recorded), create an after commit and a separate after delete event handler on the Expense entity that updates the Category total after an expense is committed or deleted. This scenario is still relatively expensive, but the category total attribute is only being recalculated when it changes vs. every time it is retrieved
If the expense totals can be updated periodically (maybe nightly or twice a day or hourly), you can create a scheduled event that re-totals each category when it runs. This is probably the least expensive approach (depending on how often the scheduled event runs), but the tradeoff is that the category totals are out of date between scheduled event runs. This may or may not be OK based on your business requirements.