ofbiz-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lns_lns <...@gmx.ch>
Subject Re: Need help with entity and grouping/ordering
Date Wed, 03 Jun 2009 18:04:09 GMT

well, there is no log, sure there is one, but it won't help since no error
was shown, the findList() works fine. However it doesn't return the correct
values:

basically this: 

SELECT s2, s1, s3 FROM
(SELECT SOMETABLE.SOMETHING2 AS s2, SOMETABLE.SOMETHING1 AS s1,
SOMETABLE.SOMETHING3 AS s3
FROM SOMETABLE ORDER BY SOMETHING1,SOMETHING2) b
WHERE s1='something'
GROUP BY s1

returns something else then this:

<view-entity entity-name="groupsometable" package-name="org.ofbiz"
title="virtual products Entity">
		<member-entity entity-alias="s" entity-name="sometable" />
		<alias entity-alias="s" name="s1" group-by="true"/>
		<alias entity-alias="s" name="s2" />
		<alias entity-alias="s" name="s3"/>
</view-entity>

+

condlist.add(EntityCondition.makeCondition("s1",
EntityComparisonOperator.NOT_EQUAL,null));
conds = EntityCondition.makeCondition(condlist);
delegator.findList("groupsometable", conds, null,
UtilMisc.toList("s1","s2"), null, false);

in the entitydef/bsh version the result is first grouped and then ordered,
whereas in the mysql query above the result is first ordered and then
grouped, however it's a SQL workaround.
so it's definetely not a bug, i just need help implementing the query in
bsh...


BJ Freeman wrote:
> 
> Could you include the log from the findlist error.
> there maybe another reason that the query did not work.
> there are examples of find on a view entity that has groupby and then
> used an orderby in the find.
> so I find it hard to believe that it will not work with mysql.
> 
> 
> lns_lns sent the following on 6/2/2009 9:50 AM:
>> I am talking about the output in ofbiz, the query beeing used is allready
>> working fine in mysql,  however if i implement it with beanshell (view
>> entity with group-by true, and delegator.findList()) it won't work since
>> the
>> output gets first grouped and then ordered...
>> you can't see an entity related solution, since that's where i'm stuck
>> fyi this has nothing to do with mysql itself it's just about
>> beanshell/ofbiz
>> ;D
>> 
>> BJ Freeman wrote:
>>> I am sorry I am lost
>>> are you talking about the output of ofbiz from ofbiz data layer to mysql
>>> or just sql.
>>>
>>> you solutions is sql only I did not see a entity related one.
>>>
>>>
>>> lns_lns sent the following on 6/2/2009 4:35 AM:
>>>> Hi BJ,
>>>> i know how to order and group an resutlset...
>>>> however i need to order before grouping, that's not the way mysql
>>>> supports
>>>> it...
>>>> wich is why there's the workaround i posted, so my real problem is not
>>>> ordering but nesting two querys
>>>> if you test the query i wrote and compare it with the result of the
>>>> "normal"
>>>> qeury (order and group in the same select statement) you will see the
>>>> difference, since mysql first groups and the orders...
>>>> so, you got some tip on how to accomplish the effect?
>>>> thanks!
>>>>
>>>> BJ Freeman wrote:
>>>>> Entities, as well as View entities define data storage and
>>>>> relationship
>>>>> to other entities.
>>>>> the findby and orderby are methods/attributes taken on the entity.
>>>>>
>>>>> do a search of the code for orderby or groupby
>>>>> you will get a lot of examples.
>>>>>
>>>>>
>>>>> lns_lns sent the following on 6/1/2009 5:09 AM:
>>>>>> Hi,
>>>>>> I have to select some columns from a table they have to be, first
>>>>>> ordered,
>>>>>> then grouped.
>>>>>> in SQL it looks like this:
>>>>>>
>>>>>> SELECT s2, s1, s3 FROM
>>>>>> (SELECT SOMETABLE.SOMETHING2 AS s2, SOMETABLE.SOMETHING1 AS s1,
>>>>>> SOMETABLE.SOMETHING3 AS s3
>>>>>> FROM SOMETABLE ORDER BY SOMETHING1,SOMETHING2) b
>>>>>> WHERE s1='something'
>>>>>> GROUP BY s1
>>>>>>
>>>>>> When queried directly to the MYSQL server this works perfectly, but
>>>>>> now
>>>>>> i
>>>>>> have to implement it in ofbiz, bsh..
>>>>>> First I wanted to create two "view-entity"s but I couldn't find
>>>>>> anything
>>>>>> about order-by in the alias tag.
>>>>>> Is there a way to add an ORDER BY clause in the <alias>?
>>>>>> If not, is there another way to get this query ported to ofbiz?
>>>>>>
>>>>>> Thanks in advance!
>>>>>> Regards
>>>>>> Lukas
>>>>> -- 
>>>>> BJ Freeman
>>>>> http://www.businessesnetwork.com/automation
>>>>> http://bjfreeman.elance.com
>>>>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
>>>>> Systems Integrator.
>>>>>
>>>>>
>>>>>
>>> -- 
>>> BJ Freeman
>>> http://www.businessesnetwork.com/automation
>>> http://bjfreeman.elance.com
>>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
>>> Systems Integrator.
>>>
>>>
>>>
>> 
> 
> -- 
> BJ Freeman
> http://www.businessesnetwork.com/automation
> http://bjfreeman.elance.com
> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
> Systems Integrator.
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Need-help-with-entity-and-grouping-ordering-tp23814009p23856177.html
Sent from the OFBiz - User mailing list archive at Nabble.com.


Mime
View raw message