Grouping

The Query API includes the groupBy method that groups retrieved objects by field, with objects with the same field value placed into the same group. A group is represented by a Grouping object, and the groupBy method returns a list of Grouping objects. (There’s also the groupByPartial method that specifies a limit on the number of Grouping objects returned.)

The following snippets use the Activity class to demonstrate the groupBy method.

public class Activity extends Record {
    @Indexed private Date activityDate;
    @Indexed private User activityUser;
    @Indexed private String groupLead;

     /* Getters and setters */
 }

The following query returns all Activity instances and groups them by their activityDate values. That is, all instances with the same activityDate value are grouped together.

Iterating the list of Grouping objects, the code calls methods on each object. The getKeys method returns the value of the field specified in the Query.groupBy method, in this case activityDate. The getCount method returns the number of Activity objects in the group.

List<Grouping<Activity>> groupings = Query.from(com.psddev.dari.test.Activity.class)
                                          .groupBy("activityDate");
/* Iterate groups */
for (Grouping grouping : groupings) {
    Date date = (Date) grouping.getKeys().get(0);
    System.out.println("Date: " + date.toString());
    long count = grouping.getCount();
    System.out.println("Count: " + count + "\n");
}

You can sort the list of Grouping objects that are returned.

List<Grouping<Activity>> groupings = Query.from(com.psddev.dari.test.Activity.class)
                                           .sortDescending("activityDate")
                                           .groupBy("activityDate");

The Grouping class also includes the createItemsQuery() method. This method returns a Query object that retrieves all of the objects in a group. In the following snippet, createItemsQuery() returns all of the Activity objects in the group that’s currently being iterated.

List<Grouping<Activity>> groupings = Query.from(com.psddev.dari.test.Activity.class).
                                            groupBy("activityDate");
/* Iterate groups */
for (Grouping grouping : groupings) {
    Date date = (Date) grouping.getKeys().get(0);
    System.out.println("Date: " + date.toString());
    long count = grouping.getCount();
    System.out.println("Count: " + count);

    List<Activity> activities = grouping.createItemsQuery().selectAll();
    /* Iterate activities in each group */
    for (Activity activity : activities) {
        System.out.println("Date: " + activity.getActivityDate().toString());
        System.out.println("User: " + activity.getUser().getUserName());
    }
}

You can group objects by more than one field. The following snippet returns all Activity instances and groups them by the activityDate field and the activityUser field.

List<Grouping<com.psddev.dari.test.Activity>> groupings = 
    Query.from(com.psddev.dari.test.Activity.class)
         .sortAscending("activityDate")
         .groupBy("activityDate", "activityUser");

for (Grouping grouping : groupings) {
    Date date = (Date) grouping.getKeys().get(0);
    System.out.println("Date: " + date.toString());
    System.out.println ("User: " + ((User) grouping.getKeys().get(1)).getUserName());
}