The following documents are synced down from sync gateway:
{ "type": "order", userId: 1, productId: 2, groupId 1}
{ "type": "product", productId: 2, productName: 'product 2 name'}
{ "type: "group", groupId: 1, groupName: 'group Name'}
each document has more fields but the relation looks as in the above. I want to return all orders but detailed:
{userId: 1, productId: 2, groupId 1, productName: 'product 2 name', groupName: 'group Name'}
My main issue so far is I can’t have a second Join.join() in the query builder. Here is what i tried so far:
DataSource productsDS = DataSource.database(database).as("productsDS");
DataSource ordersDS = DataSource.database(database).as("ordersDS");
// cant find a way to using this data source and expression
DataSource groupsDS = DataSource.database(database).as("groupsDS");
Expression groupIdExpr = Expression.property("groupId").from("groupsDS");
Expression expression = Expression.property("type").from("ordersDS").equalTo(Expression.string("order"))
.and(Expression.property("type").from("productsDS").equalTo(Expression.string("product")))
.and(Expression.property("type").from("groupsDS").equalTo(Expression.string("group")));
Query query = QueryBuilder.select(
SelectResult.expression(Meta.id.from("OrderDS")),
SelectResult.expression(Expression.property("productId").from("OrderDS")),
SelectResult.expression(Expression.property("productName").from("productsDS")),
SelectResult.expression(Expression.property("groupId").from("OrderDS")),
SelectResult.expression(Expression.property("groupName").from("groupsDS"))
).from(ordersDS)
.join(Join.join(productsDS)
.on(Expression.property("productId").from("ordersDS").equalTo(Expression.property("productId").from("productsDS"))))
.where(expression);