EjbQl Example needed...

[griffitm.michael.griffith.fda.hhs.gov]

Hello All,

I have an EntityQuery object that I need some help with ordering the data. The Entities look like

Goal -- 1.M Actions -- M,M Deliverable
Goal has a set of actions (OneToMany)
Actions have a set of Deliverables (ManyToMany)
Deliverables have a set of Actions (ManyToMany)

As a tree the Entities would look like this:

Goal
    ..Action
            ..Deliverable
            ..Deliverable
    ..Action
            ..Deliverable
            ..Deliverable
            ..Deliverable

Each branch and leaf of the tree has a field called sortOrder, so I can force the tree to sort in the order that the branches are related.

If I were writing plain old SQL, I would structure my query something like this:

SELECT 
     left(goal.description, 50) as goal_description,
     left(action.description, 50) as action_description,
     left(deliverable.description, 50) as description, 
     goal.SORT_ORDER,
     action.SORT_ORDER,
     deliverable.SORT_ORDER
FROM deliverable, action, deliverable_action, goal where ((deliverable.id = deliverable_action.deliverable_id and action.id = deliverable_action.action_id) and action.goal = goal.id)
GROUP BY goal.SORT_ORDER, action.SORT_ORDER,  deliverable.SORT_ORDER 
ORDER BY goal.SORT_ORDER, action.SORT_ORDER,  deliverable.SORT_ORDER ASC

How can I structure such a query using EjbQl? I want to retrieve the list of deliverables ordering them by the Goal.sortOrder, Action.SortOrder and Deliverable.SortOrder. I see there is a way to provide a groupBy() String to the query, but I am unsure how this is handled when the groupBy contains a collection, as my groupBy would.  I'm not sure if I'm making any sense, but any help would be much appreciated.

Thanks in advance,
MG