Improving the code that prepares select fields for view entities

[Jacopo Cappellato-4]

Hi,

I'm moving here part of my reply to a message in the user list with an  
idea to improve the code that prepares select fields for view entities.

What do you think?

Jacopo


Begin forwarded message:

> From: Jacopo Cappellato <[hidden email]>
> Date: September 18, 2008 10:00:29 AM GMT+02:00
> To: [hidden email]
> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
> Reply-To: [hidden email]
>
>
> 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
>

> [...]

>
> 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
>

[Scott Gray]

+1

Regards
Scott

2008/9/18 Jacopo Cappellato <[hidden email]>:

> Hi,
>
> I'm moving here part of my reply to a message in the user list with an idea
> to improve the code that prepares select fields for view entities.
>
> What do you think?
>
> Jacopo
>
>
> Begin forwarded message:
>
>> From: Jacopo Cappellato <[hidden email]>
>> Date: September 18, 2008 10:00:29 AM GMT+02:00
>> To: [hidden email]
>> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
>> Reply-To: [hidden email]
>>
>>
>> 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
>>
>
>> [...]
>
>>
>> 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
>>
>
>

[Jacques Le Roux]

+1

Would this resolves also https://issues.apache.org/jira/browse/OFBIZ-1944 ?

Jacques

From: "Scott Gray" <[hidden email]>

> +1
>
> Regards
> Scott
>
> 2008/9/18 Jacopo Cappellato <[hidden email]>:
>> Hi,
>>
>> I'm moving here part of my reply to a message in the user list with an idea
>> to improve the code that prepares select fields for view entities.
>>
>> What do you think?
>>
>> Jacopo
>>
>>
>> Begin forwarded message:
>>
>>> From: Jacopo Cappellato <[hidden email]>
>>> Date: September 18, 2008 10:00:29 AM GMT+02:00
>>> To: [hidden email]
>>> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
>>> Reply-To: [hidden email]
>>>
>>>
>>> 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
>>>
>>
>>> [...]
>>
>>>
>>> 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
>>>
>>
>>
>

[Jacopo Cappellato-4]

On Sep 18, 2008, at 8:15 PM, Jacques Le Roux wrote:

> +1
>
> Would this resolves also https://issues.apache.org/jira/browse/OFBIZ-1944 
>  ?
>

Yes, It should resolve that issue too.

Jacopo

> Jacques
>
> From: "Scott Gray" <[hidden email]>
>> +1
>> Regards
>> Scott
>> 2008/9/18 Jacopo Cappellato <[hidden email]>:
>>> Hi,
>>>
>>> I'm moving here part of my reply to a message in the user list  
>>> with an idea
>>> to improve the code that prepares select fields for view entities.
>>>
>>> What do you think?
>>>
>>> Jacopo
>>>
>>>
>>> Begin forwarded message:
>>>
>>>> From: Jacopo Cappellato <[hidden email]>
>>>> Date: September 18, 2008 10:00:29 AM GMT+02:00
>>>> To: [hidden email]
>>>> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
>>>> Reply-To: [hidden email]
>>>>
>>>>
>>>> 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
>>>>
>>>
>>>> [...]
>>>
>>>>
>>>> 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
>>>>
>>>
>>>
>>