Get sum of items multipied with unit price

Asking here to persist the solution.

Following situation:
object basket with:

property name="items" relationship="one-to-many" relatedto="basket_items" relationshipKey="basket";
property name="items_count" type="numeric" formula="agg:sum{ items.amount}";

object basket_items with:

property name="amount"    	type="numeric" default="0";
property name="unitprice"	type="numeric";
property name="sum"	        type="numeric" formula="${prefix}amount* ${prefix}unitprice";

The items are calculated correctly, but I’d like to get the price of the whole basket.
I tried:

property name="total"       type="numeric" formula="agg:sum{ items.sum }";

returns in: Unknown column 'items.sum' in 'field list'

Also formula="agg:sum{ items.amount * items.unitprice }" and formula="sum(${prefix}items.anzahl * ${prefix}items.nettopreis)" result in an error.

Is there a simple way to get the full amount of the whole basket?


Did you try:

formula="sum( ${prefix}items.sum )"
I haven’t got a computer in front of me right now, but this seems to be the variation you’ve not mentioned…

I seem to remember the agg: syntax deliberately omitted support for formula fields.

I did and the error is:

Unknown column 'items.sum' in 'field list'

I guess because sum itself is a formula in the related object.

I just tried the following, and it worked fine:

property name="items_total" type="numeric" formula="sum( ${prefix}items.amount * ${prefix}items.unit_price )";

Hm, tried exactly this yesterday with an array, now it works fine.

Thanks and sorry for bothering