I have the following documents:
[ {CatId: 1, "type":"group", "minValue": 100, "currentValue": 80, "groupId":1}, {CatId:1, "type":"member","minValue": 50,"currentValue": 50, "memberId":1} ]
The output I want is complex but I want to start simpler. The ideal output data grouped by catId
CatId: 1, groupsTotalMinValue: 100, groupsTotalCurrentVal: 80, groupsTotal: 1, membersTotal: 1, totalBalancedGroups:0, totalBalancedClients:1
Balanced results are for groups/members with minValue and currentValue the same.
Anyway, to get things easier I just want to get groupsTotalMinValue, groupsTotalCurrentVal,groupsTotal and membersTotal for now and I tried the following join but the numbers are very high:
` Query query = QueryBuilder.select(
SelectResult.expression(Function.sum(Expression.property("minValue").from("groups"))).as("groupsTotalMinValue"),
SelectResult.expression(Function.sum(Expression.property("currentValue").from("groups"))).as("groupsTotalCurrentValue"),
SelectResult.expression(Function.count(Expression.property("groupId").from("groups"))).as("groupsTotal"),
SelectResult.expression(Function.count(Expression.property("memberId").from("members"))).as("membersTotal"),
SelectResult.expression(Expression.property("CatId").from("groups")).as("CatId"))
.from(DataSource.database(database).as("groups"))
.join(Join.join(DataSource.database(database).as("members")).on(Expression.property("CatId").from("groups").equalTo(Expression.property("CatId").from("members"))))
.where(Expression.property("type").from("groups").equalTo(Expression.string("group"))
.and(Expression.property("type").from("members").equalTo(Expression.string("member")))
.and(Expression.property("CatId").from("members").equalTo(Expression.property("CatId").from("groups")))
.and(Expression.property("DistrictId").from("members").equalTo(Expression.intValue(districtId))))
.groupBy(Expression.property("CatId").from("groups"));`