Need help with entity and grouping/ordering

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Need help with entity and grouping/ordering

lns_lns
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
Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

BJ Freeman
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.

Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

lns_lns
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.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

BJ Freeman
In reply to this post by BJ Freeman
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.

Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

BJ Freeman
In reply to this post by BJ Freeman
I am guessing your are more interested in the sql and mysql.
I think you will get better support in this if you go to mysql support.
I personally use postgresql, so I am not much help on mysql.
:D

BJ Freeman sent the following on 6/2/2009 4:45 AM:

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

Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

lns_lns
In reply to this post by BJ Freeman
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.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

BJ Freeman
In reply to this post by BJ Freeman
Thanks for the clarification.
that is above my head. Hopefully David will answer.

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.

Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

BJ Freeman
In reply to this post by BJ Freeman
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.

Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

lns_lns
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.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with entity and grouping/ordering

BJ Freeman
In reply to this post by BJ Freeman
the log should show the SQL generated and the sequence.
My understanding of the process is
and though you sample code is helpful it is not definite enough to do
any evaluation of conditions that my exist that are not apparent.
So if find it difficult to help.

I will say again that there are similar view-entities with groupby that
are ordered in current code. some are now in groovy files, which use to
be bsh files. so the fact that is in the bsh file should not  make any
difference.

Hope you find your answer.

lns_lns sent the following on 6/3/2009 11:04 AM:

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

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