I have documents such as
Category(type="category", catId = 1, name = "cat")
and Products with
Product(catId=1, type="product"), Product(catId=1, type="product")
Now I want to get list of all Categories (all their field) and the total number of products per category.
val catAlias = "catAlias"
val productAlias = "productAlias"
val q =
QueryBuilder
.select(
SelectResult.expression(Function.count(Expression.property("$productAlias. catId").from(productAlias))).`as`("totalProducts"),
SelectResult.all().from(catAlias),
SelectResult.expression(Meta.id.from(catAlias)).`as`("metaId"),
)
.from(
DataSource.database(readsDB).`as`(catAlias),
)
.join(
Join.leftJoin(DataSource.database(readsDB).`as`(productAlias))
.on(
Expression.property("$productAlias.type").equalTo(
Expression.string(
"product",
),
)
.and(
Expression.property("$productAlias.catId")
.equalTo(Expression.property("$catAlias.catId")),
),
),
)
.where(
Expression.property("$catAlias.type").from(catAlias)
.equalTo(Expression.string("category")),
).groupBy(
Expression.property("$catAlias.catId")
)
try {
val results = q.execute().allResults()
for(r in results){
}
}catch(e: CouchbaseLiteException) {
Log.i("error", "$e")
}
AN UPDATE: Now I can see all my categories but the total number of products for each category is always 0.