Join three documents

Mobile Couchbase Lite
1

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);
2

The syntax should be:

.join(
   Join.join(productsDS).on(Expression.property("productId").from("ordersDS").equalTo(Expression.property("productId").from("productsDS"))),
   Join.join(groupsDS).on(Expression.property("groupId").from("groupDS").equalTo(Expression.property("groupId").from("productsDS")))
)
1 Like