[SOLVED] How to use predefined SQL functions on entity find?

How to use predefined SQL functions on entity find?

With plain SQL queries, we can use all predefined SQL functions like avg(),sum() etc.. But with moqui entities, we can only get data from the database. There are no predefined functions.

Is there any other way to achieve this kind of requirements like get data(manipulated data- performed some run-time functions like avg(),sum() ) from database with entity-find?

Solution

In Moqui functions are used through a view-entity definition using the @function attribute on the alias element. The view-entity may have one or more member entities. Here is an example from mantle-usl with a single member-entity:

<view-entity entity-name="AssetQuantitySummary" package="mantle.product.asset">
    <member-entity entity-alias="AST" entity-name="mantle.product.asset.Asset"/>
    <alias-all entity-alias="AST"><exclude field="quantityOnHandTotal"/><exclude field="availableToPromiseTotal"/>
        <exclude field="originalQuantity"/></alias-all>
    <alias name="quantityOnHandTotal" entity-alias="AST" function="sum"/>
    <alias name="availableToPromiseTotal" entity-alias="AST" function="sum"/>
    <alias name="originalQuantity" entity-alias="AST" function="sum"/>
</view-entity>

There are many more complex view-entity definitions in mantle-usl that you can use in your code, or that you can use as examples. These include more complex queries with sub-selects, nested functions, etc.