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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
