0 Replies Latest reply on May 13, 2008 10:45 PM by Michael Griffith

    EjbQl Example needed...

    Michael Griffith Newbie

      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