Grouping and specifying order

Grouping and setting order

DbAssist provides support for adding GROUP BY and ORDER BY keywords to the SQL query. Following examples show explanatory use cases.

Group By

Simple use for one entity

First let's save example users data into DB:

saveUsersData(uRepo, new ArrayList<User>() {{
    add(new User(1, "Mont", ExampleDate, 14.5, "worker"));
    add(new User(2, "Mont", ExampleDate, 10.1, "worker"));
    add(new User(3, "Rose", ExampleDate, 1.5, "worker"));
    add(new User(4, "Rose", ExampleDate, 111.5, "boss"));
}});

Now we have to create a ConditionsBuilder and a SelectionList which keeps the information about which aggregate functions to calculate when grouping is applied and to which entity (the entity corresponding to the specified ConditionsBuilder

ConditionsBuilder cb = new ConditionsBuilder();
Condition hc = cb.equal("category", "worker");
cb.apply(hc);

SelectionList selectionList = new SelectionList();
selectionList
        .sum(cb, "salary")
        .avg(cb, "salary")
        .count(cb, "id")

We specify grouping as shown below. Here we are using only one Group By statement, so we are calling the groupBy method once (for a field category from the entity corresponding to cb, here it is a User entity).

GroupBy groupBy = new GroupBy();
groupBy.groupBy(cb, "category");
);

Then we can call findAttributes method, remembering to pass all the information about the query:

List<Tuple> results = uRepo.findAttributes(selectionList, cb, groupBy);
Tuple groupWorkersResults = results.get(0);

Double sumSalaryWorkers = (Double) groupWorkersResults.get(0);
Double avgSalaryWorkers = (Double) groupWorkersResults.get(1);
Long numWorkers = (Long) groupWorkersResults.get(2);

We are using here a method findAttributes, which instead of returning the whole entity, gives us a list of tuples containing the values specified inside SelectionList.

Result:

SELECT
 SUM(users.salary)
 AVG(users.salary)
 COUNT(users.id)
FROM users
WHERE users.category = 'worker'
GROUP BY users.category

Order By

We specify the sorting keywords by using OrderBy class, which allows us to specify the sorting order by attributes from multiple entities. In order to add the ascending or descending order, we use either asc or desc methods. These methods take the ConditionsBuilder parameter (corresponding to the entity, from which the attribute comes from) and a String - name of the attribute from this entity.

The sequence of calling the methods matters; the keywords appear in the generated SQL query in the same order.

//joins
ConditionsBuilder rootBuilder = new ConditionsBuilder();
ConditionsBuilder builderCerts = rootBuilder.join("certificates", JoinType.LEFT);

//sorting
OrderBy orderBy = new OrderBy();
orderBy
        .asc(rootBuilder, "name")
        .desc(rootBuilder, "id")
        .asc(builderCerts, "id")
        .desc(rootBuilder, "createdAt");

List<User> results = uRepo.find(rootBuilder, orderBy);

Result:

SELECT * FROM users
ORDER BY 
users.name ASC, 
users.id DESC, 
certificates.id ASC, 
users.createdAt DESC

Group By and Order By on multiple joined entities

As mentioned before, it is possible to put Order By and Group By keywords on the attributes (fields) from different joined entities. The following example shows a simple use:

//joins
ConditionsBuilder cb = new ConditionsBuilder();
ConditionsBuilder builderCerts = cb.join("certificates", JoinType.LEFT);

//select
SelectionList selectionList = new SelectionList();
selectionList.avg(cb, "salary");

//group by
GroupBy groupBy = new GroupBy();
groupBy
        .groupBy(cb, "category")
        .groupBy(builderCerts, "name");

//order by
OrderBy orderBy = new OrderBy();
orderBy
        .asc(cb, "category")
        .asc(builderCerts, "name");

List<Tuple> result = uRepo.findAttributes(selectionList, cb, orderBy, groupBy);

It is important to remember, that the field(s) specified in orderBy have to be contained in the group of the ones previously specified in groupBy.

Result:

SELECT AVG(users.salary) FROM users
...
... JOIN ...
...
GROUP BY
users.category,
certificates.name
ORDER BY
users.category,
certificates.name