Error in group-by in view-entity

[ian tabangay]

Hi. I have this view-entity:

  <view-entity entity-name="OrderItemGroupByProduct"
package-name="ph.ati.ofbiz.order">
    <member-entity entity-alias="ORIT" entity-name="OrderItem"/>
    <alias name="orderId" entity-alias="ORIT" group-by="true"/>
    <alias name="productId" entity-alias="ORIT" group-by="true"/>
    <alias name="quantity" entity-alias="ORIT" group-by="true"
function="sum"/>
    <relation type="one" rel-entity-name="OrderHeader">
      <key-map field-name="orderId" />
    </relation>
    <relation type="one" rel-entity-name="Product">
      <key-map field-name="productId" />
    </relation>
  </view-entity>

What I wanted to have is a row containing the sum of quantity ordered for
each productId and orderId.
The error comes from calling 'arraySize = (int)
delegator.findCountByCondition(entityName, condition, null, null);'
when it is executing this sql:
'SELECT COUNT(1) FROM (SELECT COUNT(1) FROM public.ORDER_ITEM ORIT GROUP BY
ORIT.ORDER_ID, ORIT.PRODUCT_ID, SUM(ORIT.QUANTITY)) TEMP_NAME'
How should I make the view-entity?

~ ian

[BJ Freeman]

I know in the cart a SECA is called to update the grand total in the
header of the order.
I would do  widget that has a bsh that does the calculations for you.
or a FTL that uses a bsh or groovy script.



ian tabangay sent the following on 9/10/2008 10:25 PM:

> Hi. I have this view-entity:
>
>   <view-entity entity-name="OrderItemGroupByProduct"
> package-name="ph.ati.ofbiz.order">
>     <member-entity entity-alias="ORIT" entity-name="OrderItem"/>
>     <alias name="orderId" entity-alias="ORIT" group-by="true"/>
>     <alias name="productId" entity-alias="ORIT" group-by="true"/>
>     <alias name="quantity" entity-alias="ORIT" group-by="true"
> function="sum"/>
>     <relation type="one" rel-entity-name="OrderHeader">
>       <key-map field-name="orderId" />
>     </relation>
>     <relation type="one" rel-entity-name="Product">
>       <key-map field-name="productId" />
>     </relation>
>   </view-entity>
>
> What I wanted to have is a row containing the sum of quantity ordered for
> each productId and orderId.
> The error comes from calling 'arraySize = (int)
> delegator.findCountByCondition(entityName, condition, null, null);'
> when it is executing this sql:
> 'SELECT COUNT(1) FROM (SELECT COUNT(1) FROM public.ORDER_ITEM ORIT GROUP BY
> ORIT.ORDER_ID, ORIT.PRODUCT_ID, SUM(ORIT.QUANTITY)) TEMP_NAME'
> How should I make the view-entity?
>
> ~ ian
>

[ian tabangay]

Ah I see. thanks for the info. But I would still would want to know where
the error is coming from if Ill be doing other groupings like this. Further
more, Id like to do some simple calculations (like summations) thru the
database to lessen (even by a little bit) the load on the application.

~ ian

On Thu, Sep 11, 2008 at 1:34 PM, BJ Freeman <[hidden email]> wrote:

> I know in the cart a SECA is called to update the grand total in the
> header of the order.
> I would do  widget that has a bsh that does the calculations for you.
> or a FTL that uses a bsh or groovy script.

[ian tabangay]

Found it.
<alias name="quantity" entity-alias="ORIT" group-by="true" function="sum"/>
should be
<alias name="quantity" entity-alias="ORIT" function="sum"/>

~ ian

On Thu, Sep 11, 2008 at 1:25 PM, ian tabangay <[hidden email]> wrote:

> Hi. I have this view-entity:
>
>   <view-entity entity-name="OrderItemGroupByProduct"
> package-name="ph.ati.ofbiz.order">
>     <member-entity entity-alias="ORIT" entity-name="OrderItem"/>
>     <alias name="orderId" entity-alias="ORIT" group-by="true"/>
>     <alias name="productId" entity-alias="ORIT" group-by="true"/>
>     <alias name="quantity" entity-alias="ORIT" group-by="true"
> function="sum"/>
>     <relation type="one" rel-entity-name="OrderHeader">
>       <key-map field-name="orderId" />
>     </relation>
>     <relation type="one" rel-entity-name="Product">
>       <key-map field-name="productId" />
>     </relation>
>   </view-entity>
>
> What I wanted to have is a row containing the sum of quantity ordered for
> each productId and orderId.
> The error comes from calling 'arraySize = (int)
> delegator.findCountByCondition(entityName, condition, null, null);'
> when it is executing this sql:
> 'SELECT COUNT(1) FROM (SELECT COUNT(1) FROM public.ORDER_ITEM ORIT GROUP
> BY ORIT.ORDER_ID, ORIT.PRODUCT_ID, SUM(ORIT.QUANTITY)) TEMP_NAME'
> How should I make the view-entity?
>
> ~ ian
>