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

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 SelectionList, which keeps information about which aggregate functions to calculate when grouping is applied.

AbstractRepository.SelectionList<User> selectionList = (builder, root) -> Arrays.asList(
        builder.sum(root.get("salary")),
        builder.avg(root.get("salary")),
        builder.count(root.get("id"))
);

We specify grouping with a list of lambdas, as shown below. Here we are using only one Group By statement, so we are passing a list containing only one lambda.

AbstractRepository.GroupBy<User> groupBy = (root) -> Arrays.asList(
        root.get("category")
);

Finally, let's add a condition so that we get the results for only "worker" category.

ConditionsBuilder conditions = new ConditionsBuilder();
HierarchyCondition hc = conditions.equal("category", "worker");
conditions.apply(hc);

List<Tuple> results = uRepo.findAttributes(selectionList, conditions, null, null, 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

Similarly as before, we specify order by passing a list of lambdas.

AbstractRepository.OrderBy<User> userOrderBy = (builder, root) -> Arrays.asList(
        builder.asc(root.get("name")),
        builder.desc(root.get("createdAt"))
);
List<User> results = uRepo.find(new ConditionsBuilder(), null, userOrderBy);

Result:

SELECT * FROM users
ORDER BY users.name ASC, users.created_at DESC