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.
