Joining three documents

Mobile Couchbase Lite
1

I have seen this https://www.couchbase.com/forums/t/join-three-documents/31166 which I hoped would solve my issue but it didn’t. Basically, I expect only two documents but I am getting a dozen. Here are my document types:

{type:'parent', parentId: 1, parentName: 'parent name1'}
{type:'parent', parentId: 2, parentName: 'parent name2'}
{type:'child', parentId: 1, childName: ' Child 1'}
{type:'child', parentId: 2, childName: ' Child 2'}
{type:'player', parentId: 1, childId: 1, playerId: 10}
{type:'player', parentId: 1, childId: 2, playerId: 10}

basically, player documents select a parent and a child to create their own parent-child relationship. So in the above, player with id 10 has chosen to create a family with children 1 and 2 and parent id 1. Now I want to return the family for playerId 10 which should be two documents as follows:

{type:'player', parentId: 1, childId: 2, playerId: 10, parentName: 'parent name 1', childName: 'child name 2'},
{type:'player', parentId: 1, childId: 1, playerId: 10, parentName: 'parent name 1', childName: 'child name 1'}

the join statement from the above link gives me about 12 records when in reality it should return two documents for player id 10.

What would the join statements look like please?

2

Hey @rut!
I think people will be much more willing to try to help out, if you can show what you’ve tried, already and, maybe, say what you expected and what you got instead.

@Lauren_Nguyen’s answer works perfectly for the case in the post you cite, so we need some additional detail…

-blake

3

@blake.meike so sorry. you are right, i should have pasted my attempt. FOr some reason, I Cant edit my post again but here is what I have tried that is returning 12 documents, instead of two. Doing Expression type == ‘player’ and Playerid=10 only without any join gives two documents only but misses out parentName and childName obviously:

    public void chosenFamily(int playerId){
        String aliasPlayer = "playerDS";
        String aliasParent =  "parentsDS";
        String aliasChildren = "childrenDS";
        DataSource parentsDS = DataSource.database(database).as(aliasParent);
        DataSource playerDS = DataSource.database(database).as(aliasPlayer);
        DataSource childrenDS = DataSource.database(database).as(aliasChildren);
   
        Expression expression = Expression.property("type").from(aliasPlayer).equalTo(Expression.string("player"))
                .and(Expression.property("playerId").from(aliasPlayer).equalTo(Expression.intValue(playerId)))
                .and(Expression.property("type").from(aliasParent).equalTo(Expression.string("parent")))
                .and(Expression.property("type").from(aliasChildren).equalTo(Expression.string("child")));


        Query query = QueryBuilder.select(
                SelectResult.expression(Meta.id.from("playerDS")),
                SelectResult.expression(Expression.property("type").from(aliasPlayer)),
                SelectResult.expression(Expression.property("parentId").from(aliasPlayer)),
                SelectResult.expression(Expression.property("playerId").from(aliasPlayer)),
                SelectResult.expression(Expression.property("childId").from(aliasPlayer)),
                SelectResult.expression(Expression.property("parentName").from(aliasParent)),
                SelectResult.expression(Expression.property("childName").from(aliasChildren))
        ).from(playerDS)
                .join(
                        Join.join(parentsDS).on(Expression.property("parentId").from(aliasPlayer).equalTo(Expression.property("parentId").from(aliasParent))),
                        Join.join(childrenDS).on(Expression.property("parentId").from(aliasChildren).equalTo(Expression.property("parentId").from(aliasParent)))

                )
                .where(expression);

        ResultSet resultSet = null;
        try {
            resultSet = query.execute();
            logMessages(resultSet.size()); // 12 documents.
        } catch (CouchbaseLiteException e) {
            logMessages(e);
        }
    }
4

The join you are doing creates a cross product: one row in the “left” table for each row in the “right” table. Doing 3 joins is going to create a bunch of rows with the type “player” and the passed id: 12 of them, apparently.

I can suggest two ways of attacking this:

  1. Read up on how “join” works. There are several types of joins: inner, outer, left, right, union, cross, etc. There are good resources on the web, explaining what each does and how to use them. Once you understand how joins work, choose the one that you need and create a query that uses it.
  2. Experiment around until you create a query that works. Start with only one join and tune it until it produces the correct (perhaps parent) results. Once that is working, add the second join and adjust it until the results look right. Finally add to the where clause, the id selection (which should be easy, at that point), and there you go!

btw… I’m guessing that you just need an inner join.