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,