[OFBiz] Dev - conditions on view-entities

[Leon Torres-2]

I'm trying to generate a report for the ofbiz order Reports tag that
lets the user see the total sales figures grouped by product ID. For
example,

<pre>
Product               QTY   Aggregate
Store ID  Product ID  Sold  Amount
--------  ----------  ----  ---------
Webstore  GZ-1000     10    $100.00
Webstore  GZ-1001     210   $2100.00
</pre>

The user should be able to filter the results by order date and by
customer (bill to party). There are obvious problems with the aggregate
amount and adjustments, but that's an issue for later.

The main issue is how to construct this report just using tools in
ofbiz. Producing a view-entity grouped by the required fields works, but
only for the trivial case where we're listing the aggregate over _all_
orders. In order to filter by orderDate, I have to put some kind of
condition on the view, as can normally be done in SQL. For instance,

create view ohv as (select * from order_header where order_date > ?);

So my question is how do to this in ofbiz. Is there a way to add
conditions to the definition of the view-entity in entityengine.xml? Or
is there some other standard technique?

- Leon

Here's my view-entity so far. Ideally I'd like to take out the partyId
as well and make that a condition on the view, thus removing the
OrderRole join completely.

<pre>
     <view-entity entity-name="OrderReportGroupByProduct"
             package-name="org.ofbiz.order.order"
             never-cache="true"
             title="Order Report Group By Product View Entity">
       <member-entity entity-alias="OH" entity-name="OrderHeader"/>
       <member-entity entity-alias="OI" entity-name="OrderItem"/>
       <member-entity entity-alias="RL" entity-name="OrderRole"/>
       <member-entity entity-alias="PR" entity-name="Product"/>
       <alias entity-alias="OH" name="productStoreId" group-by="true"/>
       <alias entity-alias="OI" name="productId" group-by="true"/>
       <alias entity-alias="PR" name="internalName" group-by="true"/>
       <alias entity-alias="OH" name="orderStatusId" field="statusId"
group-by="true"/>
       <alias entity-alias="OH" name="orderTypeId" group-by="true"/>
       <alias entity-alias="RL" name="partyId" group-by="true"/>
       <alias entity-alias="RL" name="roleTypeId" group-by="true"/>
       <alias entity-alias="OI" name="quantity" function="sum"/>
       <alias entity-alias="OI" name="unitPrice" function="sum"/>
       <alias entity-alias="OI" name="unitListPrice" function="sum"/>
       <view-link entity-alias="OH" rel-entity-alias="OI">
         <key-map field-name="orderId"/>
       </view-link>
       <view-link entity-alias="OH" rel-entity-alias="RL">
         <key-map field-name="orderId"/>
       </view-link>
       <view-link entity-alias="OI" rel-entity-alias="PR">
         <key-map field-name="productId"/>
       </view-link>
     </view-entity>
</pre>
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

You don't do it in the view entity definition, you do it in the  
query, or the find operation. View entities are for defining how the  
member entities fit together, not for pre-defining a query. That is  
partially intentional because doing so limits the re-usability of the  
view entity and results in less than desirable code bloat.

-David


On Oct 27, 2005, at 2:44 PM, Leon Torres wrote:

> I'm trying to generate a report for the ofbiz order Reports tag  
> that lets the user see the total sales figures grouped by product  
> ID. For example,
>
> <pre>
> Product               QTY   Aggregate
> Store ID  Product ID  Sold  Amount
> --------  ----------  ----  ---------
> Webstore  GZ-1000     10    $100.00
> Webstore  GZ-1001     210   $2100.00
> </pre>
>
> The user should be able to filter the results by order date and by  
> customer (bill to party). There are obvious problems with the  
> aggregate amount and adjustments, but that's an issue for later.
>
> The main issue is how to construct this report just using tools in  
> ofbiz. Producing a view-entity grouped by the required fields  
> works, but only for the trivial case where we're listing the  
> aggregate over _all_ orders. In order to filter by orderDate, I  
> have to put some kind of condition on the view, as can normally be  
> done in SQL. For instance,
>
> create view ohv as (select * from order_header where order_date > ?);
>
> So my question is how do to this in ofbiz. Is there a way to add  
> conditions to the definition of the view-entity in  
> entityengine.xml? Or is there some other standard technique?
>
> - Leon
>
> Here's my view-entity so far. Ideally I'd like to take out the  
> partyId as well and make that a condition on the view, thus  
> removing the OrderRole join completely.
>
> <pre>
>     <view-entity entity-name="OrderReportGroupByProduct"
>             package-name="org.ofbiz.order.order"
>             never-cache="true"
>             title="Order Report Group By Product View Entity">
>       <member-entity entity-alias="OH" entity-name="OrderHeader"/>
>       <member-entity entity-alias="OI" entity-name="OrderItem"/>
>       <member-entity entity-alias="RL" entity-name="OrderRole"/>
>       <member-entity entity-alias="PR" entity-name="Product"/>
>       <alias entity-alias="OH" name="productStoreId" group-by="true"/>
>       <alias entity-alias="OI" name="productId" group-by="true"/>
>       <alias entity-alias="PR" name="internalName" group-by="true"/>
>       <alias entity-alias="OH" name="orderStatusId"  
> field="statusId" group-by="true"/>
>       <alias entity-alias="OH" name="orderTypeId" group-by="true"/>
>       <alias entity-alias="RL" name="partyId" group-by="true"/>
>       <alias entity-alias="RL" name="roleTypeId" group-by="true"/>
>       <alias entity-alias="OI" name="quantity" function="sum"/>
>       <alias entity-alias="OI" name="unitPrice" function="sum"/>
>       <alias entity-alias="OI" name="unitListPrice" function="sum"/>
>       <view-link entity-alias="OH" rel-entity-alias="OI">
>         <key-map field-name="orderId"/>
>       </view-link>
>       <view-link entity-alias="OH" rel-entity-alias="RL">
>         <key-map field-name="orderId"/>
>       </view-link>
>       <view-link entity-alias="OI" rel-entity-alias="PR">
>         <key-map field-name="productId"/>
>       </view-link>
>     </view-entity>
> </pre>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Leon Torres-2]

Well, here is my problem: I want to do a query on OrderItem where I sum
the quantity field and group by the productId field, but I also need to
filter this by a date range.  What is the best way to do this?

I tried put orderDate in my view-entity to group-by="false" but got the
error:
ERROR: column "oh.order_date" must appear in the GROUP BY clause or be
used in an aggregate function

- Leon

David E. Jones wrote:
>
> You don't do it in the view entity definition, you do it in the  query,
> or the find operation. View entities are for defining how the  member
> entities fit together, not for pre-defining a query. That is  partially
> intentional because doing so limits the re-usability of the  view entity
> and results in less than desirable code bloat.
>
> -David
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Si Chen-2]

David,

This is what we found:

In a SQL query, you can have a field be part of a condition but not in
the select or group by.  So for example you can do
select productId, sum(quantity) from order_item where order_date between
'2005-10-01' and '2005-10-31' group by productId  (or something like it)

But if it is a field in the view-entity, it must either have a function
or group-by.  In other words, there is no way to reference a field just
in the condition.

Well, almost no way.  Leon found this: he can reference directly with
the table alias and field name, like "RL.party_id".  But I think that is
too much of a hack--if the field name conventions change from one db to
another, this query would break.

What do you think?

Leon Torres wrote:

> Well, here is my problem: I want to do a query on OrderItem where I
> sum the quantity field and group by the productId field, but I also
> need to filter this by a date range.  What is the best way to do this?
>
> I tried put orderDate in my view-entity to group-by="false" but got
> the error:
> ERROR: column "oh.order_date" must appear in the GROUP BY clause or be
> used in an aggregate function
>
> - Leon
>
> David E. Jones wrote:
>
>>
>> You don't do it in the view entity definition, you do it in the  
>> query, or the find operation. View entities are for defining how the  
>> member entities fit together, not for pre-defining a query. That is  
>> partially intentional because doing so limits the re-usability of
>> the  view entity and results in less than desirable code bloat.
>>
>> -David
>
>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

If you are doing grouping all fields that do not have a function must  
have group-by="true", including that date field, IF it is selected.  
One way to get around this is to explicitly specify which fields to  
select, and just don't select that date field, and make sure ALL  
fields being selected have group-by="true".

-David


On Oct 27, 2005, at 3:53 PM, Leon Torres wrote:

> Well, here is my problem: I want to do a query on OrderItem where I  
> sum the quantity field and group by the productId field, but I also  
> need to filter this by a date range.  What is the best way to do this?
>
> I tried put orderDate in my view-entity to group-by="false" but got  
> the error:
> ERROR: column "oh.order_date" must appear in the GROUP BY clause or  
> be used in an aggregate function
>
> - Leon
>
> David E. Jones wrote:
>
>> You don't do it in the view entity definition, you do it in the  
>> query, or the find operation. View entities are for defining how  
>> the  member entities fit together, not for pre-defining a query.  
>> That is  partially intentional because doing so limits the re-
>> usability of the  view entity and results in less than desirable  
>> code bloat.
>> -David
>>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Leon Torres-2]

Great! This worked, thanks for your help. We'll be adding these reports
to ofbiz shortly.

For anyone else that's searching the lists: Some good examples of
creating reports using group-by can be found in the order manager's
Reports widgets.

- Leon

David E. Jones wrote:
>
> If you are doing grouping all fields that do not have a function must  
> have group-by="true", including that date field, IF it is selected.  One
> way to get around this is to explicitly specify which fields to  select,
> and just don't select that date field, and make sure ALL  fields being
> selected have group-by="true".
>
> -David
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev