You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Vasily Ivanov <ba...@gmail.com> on 2006/03/08 09:31:26 UTC
Report Query in ORDER BY
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
Re: Report Query in ORDER BY
Posted by Armin Waibel <ar...@apache.org>.
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 <jb...@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 <jb...@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
Re: Report Query in ORDER BY
Posted by Vasily Ivanov <ba...@gmail.com>.
ok. I have no idea what "jira" is. Could you give me the link?
On 3/10/06, Jakob Braeuchi <jb...@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 <jb...@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
Re: Report Query in ORDER BY
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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 <jb...@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
Re: Report Query in ORDER BY
Posted by Vasily Ivanov <ba...@gmail.com>.
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 <jb...@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
Re: Report Query in ORDER BY
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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