Bestseller : OrderReportSalesGroupByProduct

[Eric DE MAULDE]

Hello,

I would like to introduce into my web site the best sales by product
I try to use (in a groovy file) :

delegator.findByAnd("OrderReportSalesGroupByProduct", [productStoreId : productStoreId])

I have the following error :

Error rendering screen [component://mobilier/widget/CatalogScreens.xml#categorydetail]: org.ofbiz.base.util.GeneralException: Error running Groovy script at location [component://mobilier/webapp/mobilier/WEB-INF/actions/catalog/CategoryDetail.groovy] (SQL Exception while executing the following:SELECT OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME, OH.ORDER_DATE, OH.STATUS_ID, OI.STATUS_ID, OH.ORDER_TYPE_ID, RL.PARTY_ID, RL.ROLE_TYPE_ID, SUM(OI.QUANTITY), SUM(OI.UNIT_PRICE) FROM (((public.ORDER_HEADER OH INNER JOIN public.ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID) INNER JOIN public.ORDER_ROLE RL ON OH.ORDER_ID = RL.ORDER_ID) INNER JOIN public.PRODUCT PR ON OI.PRODUCT_ID = PR.PRODUCT_ID) INNER JOIN public.PRODUCT_STORE PS ON OH.PRODUCT_STORE_ID = PS.PRODUCT_STORE_ID WHERE (OH.PRODUCT_STORE_ID = ?) GROUP BY OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME (ERROR: column "oh.order_date" must appear in the GROUP BY clause or be used in an aggregate function))

I read http://www.nabble.com/how-to-see-data-in-view-table--td17765415.html#a17765415

How can I order by quantity ?
Do you have a solution ?

Thanks

Eric

[Malin Nicolas]

Slt Eric,

If you use group by on a view, your select juste return attribute when a
group by or a fonction is use on
In your select you need only this attribute

OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME

For the quantity you need use a sum fonction on your view .
example :
      <alias entity-alias="II" name="quantityOnHandTotal" function="sum"/>

Nicolas

Eric DE MAULDE a écrit :

> Hello,
>
> I would like to introduce into my web site the best sales by product
> I try to use (in a groovy file) :
>
> delegator.findByAnd("OrderReportSalesGroupByProduct", [productStoreId : productStoreId])
>
> I have the following error :
>
> Error rendering screen [component://mobilier/widget/CatalogScreens.xml#categorydetail]: org.ofbiz.base.util.GeneralException: Error running Groovy script at location [component://mobilier/webapp/mobilier/WEB-INF/actions/catalog/CategoryDetail.groovy] (SQL Exception while executing the following:SELECT OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME, OH.ORDER_DATE, OH.STATUS_ID, OI.STATUS_ID, OH.ORDER_TYPE_ID, RL.PARTY_ID, RL.ROLE_TYPE_ID, SUM(OI.QUANTITY), SUM(OI.UNIT_PRICE) FROM (((public.ORDER_HEADER OH INNER JOIN public.ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID) INNER JOIN public.ORDER_ROLE RL ON OH.ORDER_ID = RL.ORDER_ID) INNER JOIN public.PRODUCT PR ON OI.PRODUCT_ID = PR.PRODUCT_ID) INNER JOIN public.PRODUCT_STORE PS ON OH.PRODUCT_STORE_ID = PS.PRODUCT_STORE_ID WHERE (OH.PRODUCT_STORE_ID = ?) GROUP BY OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME (ERROR: column "oh.order_date" must appear in the GROUP BY clause or be used in an aggregate function))
>
> I read http://www.nabble.com/how-to-see-data-in-view-table--td17765415.html#a17765415
>
> How can I order by quantity ?
> Do you have a solution ?
>
> Thanks
>
> Eric
>  

--
Nicolas MALIN
Consultant
Tél : [phone]
Site projet : http://www.neogia.org/
-------
Société LibrenBerry
Tél : [phone]
Site : http://www.librenberry.net/

[Jacopo Cappellato-4]

On Sep 18, 2008, at 9:25 AM, Eric DE MAULDE wrote:

> Hello,
>
> I would like to introduce into my web site the best sales by product
> I try to use (in a groovy file) :
>
> delegator.findByAnd("OrderReportSalesGroupByProduct",  
> [productStoreId : productStoreId])
>
> I have the following error :
>
> Error rendering screen [component://mobilier/widget/
> CatalogScreens.xml#categorydetail]:  
> org.ofbiz.base.util.GeneralException: Error running Groovy script at  
> location [component://mobilier/webapp/mobilier/WEB-INF/actions/
> catalog/CategoryDetail.groovy] (SQL Exception while executing the  
> following:SELECT OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID,  
> PR.INTERNAL_NAME, OH.ORDER_DATE, OH.STATUS_ID, OI.STATUS_ID,  
> OH.ORDER_TYPE_ID, RL.PARTY_ID, RL.ROLE_TYPE_ID, SUM(OI.QUANTITY),  
> SUM(OI.UNIT_PRICE) FROM (((public.ORDER_HEADER OH INNER JOIN  
> public.ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID) INNER JOIN  
> public.ORDER_ROLE RL ON OH.ORDER_ID = RL.ORDER_ID) INNER JOIN  
> public.PRODUCT PR ON OI.PRODUCT_ID = PR.PRODUCT_ID) INNER JOIN  
> public.PRODUCT_STORE PS ON OH.PRODUCT_STORE_ID = PS.PRODUCT_STORE_ID  
> WHERE (OH.PRODUCT_STORE_ID = ?) GROUP BY OH.PRODUCT_STORE_ID,  
> PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME (ERROR: column  
> "oh.order_date" must appear in the GROUP BY clause or be used in an  
> aggregate function))
>

Hello,

the OrderReportSalesGroupByProduct view-entity contains aggregate  
fields (e.g. SUM(OI.QUANTITY) ) and simple fields and they cannot  
appear as selected fields together, unless you add the simple fields  
to the group by section.
This is the meaning of the error: column "oh.order_date" must appear  
in the GROUP BY clause or be used in an aggregate function

You can do two things:
1) explicitly specify the list of fields to retrieve (passing a list  
of field names to the delegator method), and include in it only  
aggregate fields (if you don't pass the list, all the fields are  
selected, and you get this error)
2) add the simple fields you want to select to the group by section  
(again passing a list with field names to the delegator method)

Jacopo

PS: to avoid this error we may consider the following modification to  
the code that handles view entities:

* if a view entity contains aggregated fields and a "select * " query  
is run, then the list of selected fields is composed in this way:
a) all the aggregated fields are added
b) all the fields in the group by section are added

This will also resolve the error is happening in the Webtools generic  
view screen when you try to research values from a view entity with  
agrregated and non aggregated fields.

Jacopo


> I read http://www.nabble.com/how-to-see-data-in-view-table--td17765415.html#a17765415
>
> How can I order by quantity ?
> Do you have a solution ?
>
> Thanks
>
> Eric

[Eric DE MAULDE]

Hi,

Thanks Jacopo.
I've created entity screens (xml).
Bestsellers from OrderReportSalesGroupByProduct and new products (new
articles) from ProductAndCategoryMember entities work well.
I need to leak out these results when a surfer is into a sub-category.
But, in a entity sreen, I choice as operator "in", it doesn't work. I don't
have any result.
I would want to have a productCategoryId list
What class is this parameter ? related to this operator "in"

thanks

Eric
----- Original Message -----
From: "Jacopo Cappellato" <[hidden email]>
To: <[hidden email]>
Sent: Thursday, September 18, 2008 10:00 AM
Subject: Re: Bestseller : OrderReportSalesGroupByProduct

>
> On Sep 18, 2008, at 9:25 AM, Eric DE MAULDE wrote:
>
>> Hello,
>>
>> I would like to introduce into my web site the best sales by product
>> I try to use (in a groovy file) :
>>
>> delegator.findByAnd("OrderReportSalesGroupByProduct",  [productStoreId :
>> productStoreId])
>>
>> I have the following error :
>>
>> Error rendering screen [component://mobilier/widget/
>> CatalogScreens.xml#categorydetail]:
>> org.ofbiz.base.util.GeneralException: Error running Groovy script at
>> location [component://mobilier/webapp/mobilier/WEB-INF/actions/
>> catalog/CategoryDetail.groovy] (SQL Exception while executing the
>> following:SELECT OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID,
>> PR.INTERNAL_NAME, OH.ORDER_DATE, OH.STATUS_ID, OI.STATUS_ID,
>> OH.ORDER_TYPE_ID, RL.PARTY_ID, RL.ROLE_TYPE_ID, SUM(OI.QUANTITY),
>> SUM(OI.UNIT_PRICE) FROM (((public.ORDER_HEADER OH INNER JOIN
>> public.ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID) INNER JOIN
>> public.ORDER_ROLE RL ON OH.ORDER_ID = RL.ORDER_ID) INNER JOIN
>> public.PRODUCT PR ON OI.PRODUCT_ID = PR.PRODUCT_ID) INNER JOIN
>> public.PRODUCT_STORE PS ON OH.PRODUCT_STORE_ID = PS.PRODUCT_STORE_ID
>> WHERE (OH.PRODUCT_STORE_ID = ?) GROUP BY OH.PRODUCT_STORE_ID,
>> PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME (ERROR: column
>> "oh.order_date" must appear in the GROUP BY clause or be used in an
>> aggregate function))
>>
>
> Hello,
>
> the OrderReportSalesGroupByProduct view-entity contains aggregate  fields
> (e.g. SUM(OI.QUANTITY) ) and simple fields and they cannot  appear as
> selected fields together, unless you add the simple fields  to the group
> by section.
> This is the meaning of the error: column "oh.order_date" must appear  in
> the GROUP BY clause or be used in an aggregate function
>
> You can do two things:
> 1) explicitly specify the list of fields to retrieve (passing a list  of
> field names to the delegator method), and include in it only  aggregate
> fields (if you don't pass the list, all the fields are  selected, and you
> get this error)
> 2) add the simple fields you want to select to the group by section
> (again passing a list with field names to the delegator method)
>
> Jacopo
>
> PS: to avoid this error we may consider the following modification to  the
> code that handles view entities:
>
> * if a view entity contains aggregated fields and a "select * " query  is
> run, then the list of selected fields is composed in this way:
> a) all the aggregated fields are added
> b) all the fields in the group by section are added
>
> This will also resolve the error is happening in the Webtools generic
> view screen when you try to research values from a view entity with
> agrregated and non aggregated fields.
>
> Jacopo
>
>
>> I read
>> http://www.nabble.com/how-to-see-data-in-view-table--td17765415.html#a17765415
>>
>> How can I order by quantity ?
>> Do you have a solution ?
>>
>> Thanks
>>
>> Eric
>
>