How to implement an Index JOIN Clause in Couchbase Lite 2.0 using Objective-C API

Mobile
1

I have the following documents:
{
“_id”: “item:742”,
“type”: “item”
“chinese”: “技术”,
“english”: “technique”,
“pinyin”: “jìshù”,
}
{
“_id”: “ui:1234”,
“type”: “ui”,
“item”: “item:742”,
“state”: “rev”
}

Using N1QL I’m able to peform the following query:
SELECT *
FROM default items
LEFT JOIN default ui ON KEY ui.item FOR item

I have created the appropriate index so this works. Ie:
CREATE INDEXui_itemONdefault(item)

I’m now trying to implement this query using the Objective-C API for CBLv2.0 (DB23) but can’t work out how to code the join expression.

So far I have tried the following:
CBLDatabase *database = [[CBLDatabase alloc] initWithName:@“default” error:nil];

CBLQueryExpression *onItem= [[CBLQueryMeta idFrom:@"items"] equalTo:[CBLQueryExpression property:@"item" from:@"ui"]];

CBLQueryExpression *onItemAlt= [[CBLQueryExpression property:@"item" from:@"ui"] equalTo:[CBLQueryMeta idFrom:@"items"]];

CBLQueryJoin *joinUserItems = [CBLQueryJoin join:[CBLQueryDataSource database:database as:@"ui"]
                                          on:onItemAlt];

CBLQuery *query = [CBLQueryBuilder select:@[[CBLQuerySelectResult all]]
                                 from:[CBLQueryDataSource database:database as:@"items"]
                                 join:@[joinUserItems]];

NSEnumerator *results = [query execute:error];

I have tried using both onItem and onItemAlt in the join expression but neither produces any results.

Any guidance would be appreciated. I can’t find any relevant examples in the recent blog posts.

Note, I’ve simplified my example for clarity. In my application I have to query from item > ui so reversing the order is not an option.

2

Put together this unit test which does what you want and it works. I fetch Ids of joined docs. Also, you mentioned that you

Couldn’t find relevant examples in recent blog posts

. Did you check this blog out ?

- (void) testJoin {
    
    CBLMutableDocument* doc1 = [[CBLMutableDocument alloc] initWithID: @"doc1"];
    [doc1 setValue: @"foo" forKey: @"bar"];
    [self saveDocument: doc1];
    
    CBLMutableDocument* joinme = [[CBLMutableDocument alloc] initWithID: @"joinme"];
    [joinme setValue: @"doc1" forKey: @"theone"];
    [self saveDocument: joinme];
    
    CBLQuerySelectResult* MAIN_DOC_ID =
        [CBLQuerySelectResult expression: [CBLQueryMeta idFrom: @"main"]];

    
    CBLQueryExpression* on = [[CBLQueryMeta idFrom: @"main"]
                              equalTo: [CBLQueryExpression property:@"theone" from:@"secondary"]];
    CBLQueryJoin* join = [CBLQueryJoin join: [CBLQueryDataSource database: self.db as: @"secondary"]
                                         on: on];
    CBLQuery* q = [CBLQueryBuilder select: @[MAIN_DOC_ID]
                                     from: [CBLQueryDataSource database: self.db as: @"main"]
                                     join: @[join]];
    Assert(q);
    NSError* error;
    CBLQueryResultSet* rs = [q execute: &error];
    Assert(rs, @"Query failed: %@", error);
   
    for (CBLQueryResult *r in rs) {
        NSLog(@"%@",[r toDictionary]);
        AssertEqualObjects( [r valueForKey:@"id"],@"doc1");
       // You can use the doc Id to fetch the document and get relevant details
    }
 
}
3

Hi Priya,
Thanks so much for you reply. Turns out I had a mismatch between my testdb for mobile and server which was the problem.

Also I had to change my select parameter to specify the data sources:
select: @[[CBLQuerySelectResult allFrom:@"items"],[CBLQuerySelectResult allFrom:@"ui"]]

Just using:
select: @[[CBLQuerySelectResult all]]

results in an error. I’m not sure if that’s the expected behavior.

4

That behavior is expected. When you create a join you actually make two separate entities to pull data from, and the all portion needs to know which one to select its results from.

5

There seems to be a bug when trying to get all properties from the RHS when using LEFT JOIN.

Consider the modified example where I’ve added an initial document on the LHS that has no match on the RHS:

CBLDatabase *database = [[CBLDatabase alloc] initWithName:@"testdb1" error:nil];

CBLMutableDocument* doc1 = [[CBLMutableDocument alloc] initWithID: @"doc1"];
[doc1 setValue: @"foo" forKey: @"bar"];
[doc1 setValue: @"main" forKey: @"type"];
[database saveDocument: doc1 error:nil];

doc1 = [[CBLMutableDocument alloc] initWithID: @"doc2"];
[doc1 setValue: @"foo" forKey: @"bar"];
[doc1 setValue: @"main" forKey: @"type"];
[database saveDocument: doc1 error:nil];

CBLMutableDocument* joinme = [[CBLMutableDocument alloc] initWithID: @"joinme"];
[joinme setValue: @"doc1" forKey: @"theone"];
[joinme setValue: @"secondary" forKey: @"type"];
[database saveDocument: joinme error:nil];

CBLQuerySelectResult * allFromMain = [CBLQuerySelectResult allFrom:@"main"];
CBLQuerySelectResult * allFromSecondary = [CBLQuerySelectResult allFrom:@"secondary"];

CBLQueryExpression* on = [[CBLQueryMeta idFrom:@"main"] equalTo:[CBLQueryExpression property:@"theone" from:@"secondary"]];
CBLQueryJoin* join = [CBLQueryJoin leftJoin: [CBLQueryDataSource database: database as: @"secondary"]
                                     on:[on andExpression:on]];

CBLQuery* q = [CBLQueryBuilder select: @[allFromMain, allFromSecondary]
                                 from: [CBLQueryDataSource database: database as: @"main"]
                                 join:@[join]
                                where:[[CBLQueryExpression property:@"type" from:@"main"] equalTo:[CBLQueryExpression string:@"main"]]];

This will produce a Exception: EXC_BAD_ACCESS (code=1, address=0x6) presumably because of the allFrom on “secondary”.

If I replace:

CBLQuerySelectResult * allFromSecondary = [CBLQuerySelectResult allFrom:@"secondary"];

with:
CBLQuerySelectResult * allFromSecondary = [CBLQuerySelectResult expression:[CBLQueryExpression property:@"type" from:@"secondary"]];

Then it works. Is there any way to do this without specifying every property for “secondary”? I need to support a variable document model.

6

@pasin What do you think about this?

7

@pasin please let me know if you’d like me to file a Github issue if this is indeed a bug.

8

Which step do you get EXC_BAD_ACCESS? EXC_BAD_ACCESS is not expected anyway. Could you please file an issue with the detail? Thanks.

9

I was able to reproduce the issue. I filed an issue here. Thanks!

10

Thanks, I’ll keep track of the issue.