[prev in list] [next in list] [prev in thread] [next in thread] 

List:       ojb-user
Subject:    Re: Report Query in ORDER BY
From:       Armin Waibel <arminw () apache ! org>
Date:       2006-03-10 0:14:43
Message-ID: 4410C4F3.7070309 () apache ! org
[Download RAW message or body]

Vasily Ivanov wrote:
> ok. I have no idea what "jira" is. Could you give me the link?
>

It's the OJB issue tracking
http://issues.apache.org/jira/browse/OJB

regards,
Armin

> On 3/10/06, Jakob Braeuchi <jbraeuchi@gmx.ch> wrote:
>> hi vasily,
>>
>> you're right. we do not support ordering by subquery.
>> you can open a feature request on jira.
>>
>> jakob
>>
>> Vasily Ivanov schrieb:
>>> Hi Jakob,
>>>
>>> As I understand, report subquery (with Criteria.PARENT_QUERY_PREFIX +
>>> "id") can be used only in WHERE clause (Criteria class), but not in
>>> ORDER BY clause. Have a look:
>>> QueryByCriteria.addOrderBy(String, boolean)
>>> QueryByCriteria.addOrderBy(FieldHelper)
>>> QueryByCriteria.addOrderByAscending(String)
>>> QueryByCriteria.addOrderByDescending(String)
>>>
>>> ... no methods to add report subquery. :(
>>>
>>> On 3/9/06, Jakob Braeuchi <jbraeuchi@gmx.ch> wrote:
>>>
>>>> hi vasily,
>>>>
>>>> you can use Criteria.PARENT_QUERY_PREFIX to prefix an attribute of the
>>>> subquery. see QueryTest#testSubQuery3 and testSubQuery4.
>>>>
>>>> but i've to admit i nver tried it with addOrderBy().
>>>>
>>>> hth
>>>> jakob
>>>>
>>>>
>>>> Vasily Ivanov schrieb:
>>>>
>>>>> Hi All,
>>>>>
>>>>> I've got two classes:
>>>>> class Parent:
>>>>>  int id
>>>>>  String data
>>>>>  Collection children
>>>>> class Child:
>>>>>  int id
>>>>>  String data
>>>>>  int parentId
>>>>>  Parent parent
>>>>>
>>>>> I need to get collection of parents sorted by the number of children,
>>>>> but I didn't find any way to put Report Query to "ORDER BY" statement.
>>>>> Here is the code that works:
>>>>>
>>>>>
>>>>>> QueryByCriteria query = QueryFactory.newQuery(Parent.class, new Criteria());
>>>>>> query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
>>>>> AAA.PARENT_ID = A0.ID)");
>>>>>
>>>>>> persistenceManager.getBroker().getCollectionByQuery(query);
>>>>> This is generated sql:
>>>>>
>>>>>
>>>>>> SELECT A0.ID, A0.DATA, (SELECT count(1)
>>>>>                                           FROM CHILD AAA
>>>>>                                           WHERE AAA.PARENT_ID =
>>>>> A0.ID) as ojb_col_3
>>>>>
>>>>> >FROM PARENT A0
>>>>>> ORDER BY 3 DESC
>>>>> It works, but I don't like "addOrderByDescending" hard coded table
>>>>> alias "A0". There should be the way to set report subquery as OrderBy
>>>>> which should solve the problem.
>>>>> I changed code to:
>>>>>
>>>>>
>>>>>> query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE AAA.PARENT_ID = (id))");
>>>>> But it doesn't work because of "SqlHelper.splitPath()" I guess, it
>>>>> doesn't substitute "(id)" by column name "A0.ID".
>>>>>
>>>>> Any help will be greatly appreciated.
>>>>>
>>>>> Regards,
>>>>>  Vasily Ivanov
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>
>>>>>
>>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]