ejbql

EJBQL - How to sort query results by a field in LEFT JOIN

I have the following classes (simplified for clarity):

Class Top {
  InternationalStringType name;
}

Class InternationalStringType {
  List<LocalizedStringType> localizedString; 
}

Class LocalizedStringType {
  String value;
}

The following EJBQL query successfully retrieves all instance of Top with their sub-objects populated:

SELECT DISTINCT Object(t) FROM Top t LEFT OUTER JOIN t.name nm LEFT OUTER JOIN nm.localizedString nm_ls

I would like to modify the above query such that the results are sorted by Top.name.localizedString.value

What is the correct syntax to do this? I tried the following query but I get "ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list"

SELECT DISTINCT Object(t) FROM Top t LEFT OUTER JOIN t.name nm LEFT OUTER JOIN nm.localizedString nm_ls ORDER BY nm_ls.value ASC

Its not clear to me what to put in the select list for the ORDER BY clause.

Following give errors for the "," after Object(t) no matter what I put after the comma:

SELECT DISTINCT Object(t), Object(nm_ls) FROM Top t LEFT OUTER JOIN t.name nm LEFT OUTER JOIN nm.localizedString nm_ls ORDER BY nm_ls.value ASC

In case its relevant my JPA implementation is hibernate 3.6.4.Final. TIA for your help.

Solution

  • I was able to make the ORDER BY clause work with my query by simply removing the DISTINCT qualifier in the SELECT clause. I am not sure I understand the fine points but the following modified query worked perfectly!

    SELECT t FROM Top t LEFT JOIN t.name nm LEFT JOIN nm.localizedString nm_ls ORDER BY nm_ls.value ASC

    I now get a result set with Top instances sorted by the Top.name.localizedString.value attribute as I was hoping for.

    Now if any one can explain why DISTINCT does not work that would be great. Thanks.