CODEWITHSUNDEEP
javacouchbase-lite
ruth hadish
ruth hadish

Reputation: 49

couchbase lite join statement returning a lot more documents than expected

I have seen this https://forums.couchbase.com/t/join-three-documents/31166 2 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.

My code is:

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

Upvotes: 1

Views: 94

Answers (1)

J.Zhao
J.Zhao

Reputation: 64

Two questions:

  1. Are the above 6 docs all you have in the database?
  2. Could you please list all of the "gives me about 12 records?"

Upvotes: -1

Related Questions