Multiple optional keys in couchbase not work

[peng liu]

i have a user model. and has "pro" and "older",i want query "pro> 75 and 5<older<10" ,how to do that on couchbase ios? i have try set startKey and endKey but not work.
Mycode:
model：
{
@property (assign) int userOlder; //(1~10)
@property (assign) int progress; //(0~100)
}

and query code here:

//1- createView
CBLView * contactInfoView = [self.database viewNamed: @"contactDataByProgress"];
[contactInfoView setMapBlock: MAPBLOCK({
if ([doc[@"type"] isEqualToString: @"contacInfo"]) {
if (doc[@"progress"])
emit(doc[@"progress"], doc[@"userOlder"]);
}
}) version: @"6"];

//2 – make the query
CBLQuery* query = [contactInfoView createQuery];
NSLog(@"Querying older: %@ ,%@", startOlder,endOlder);
query.startKey = @[pro,startOlder];
query.endKey = @[@{},endOlder];
return query;

(My full demo code here: https://github.com/79144876/CouchbaseQuery)
Who knows?
Thanks!

[Jens Alfke]

On Sep 29, 2014, at 10:16 PM, peng liu <liup...@gmail.com> wrote:

i have a user model. and has "pro" and "older",i want query "pro> 75 and 5<older<10" ,how to do that on couchbase ios? i have try set startKey and endKey but not work.

Unfortunately you can't do that query with just an index lookup. You've got the index keys set up correctly, but the way the rows are ordered doesn't allow more than one of the keys to be range-checked. The best you can do is query over the desired range of the primary key; you'll have to filter on the secondary key yourself.

So in your example, you would set

query.startKey = @{@75, @{}}

and no endKey. You'd then iterate the results and manually test the secondary key:

for (CBLQueryRow* row in [query run: &error]) {

int older = [row.key2 intValue];

if (older > 5 && older < 10) {

… use the row …

}

}

This is actually a perfect use case for the new postFilter attribute that's available on the feature/query branch. On that branch you can modify the query like this:

query.postFilter = [NSPredicate predicateWithFormat: @"key2 > 5 and key2 < 10"];

and it will then produce only the results you want.

(And in fact this is exactly what a SQL query would do — the query planner would realize the index lookup could only filter on the primary key, so it would then internally post-filter the results to check the secondary key. The CBLQueryPlanner class that's also available on the feature/query branch will do the same thing if you give it a query like this, including designing the view for you!)

—Jens

PS: The cool new query tools on the feature/query branch are just one of the upcoming features I'll be talking about in my "The Future Of Couchbase Lite" talk at the Couchbase Connect conference next Tuesday! Be there!

[Todd Freese]

>PS: The cool new query tools on the feature/query branch are just one of the upcoming features I'll be talking about in my "The Future Of Couchbase Lite" talk at the Couchbase Connect conference next Tuesday! Be there!

Are you going to record your presentation for those of us that can not attend?

Todd

[Traun Leyden]

Hey Todd,

I'll be going to all of Jens' sessions, and I'll try to remember to bring my tripod + glyph so I can record his sessions in case there is no "official" session recording.

--
You received this message because you are subscribed to the Google Groups "Couchbase Mobile" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mobile-couchba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mobile-couchbase/0a546216-5b77-4286-852a-0dcf8f569842%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Todd Freese]

That's great. Let me know if I can help in encoding and posting to a site for others.

Todd

[Traun Leyden]

Hey Todd,

I was told that all sessions would be recorded, so I didn't bother doing it myself.  

We'll let you know once they are posted online.

On Tue, Sep 30, 2014 at 5:43 PM, Todd Freese <to...@filmworkers.com> wrote:

That's great. Let me know if I can help in encoding and posting to a site for others.

Todd

--
You received this message because you are subscribed to the Google Groups "Couchbase Mobile" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mobile-couchba...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mobile-couchbase/e97ba4e6-9946-402a-94ad-402ece5aa134%40googlegroups.com.

[Todd Freese]

Thanks! I saw jens posting. Looking forward to the videos!

T

[peng liu]

As you said.it's not support two keys to create a range.and i can filter a first keys ,and then get the first  result  then filter second keys .it same work.

but if the first range not get any result,at this time how to filter second keys ?

so .i do not think it's a good idea.

thanks

在 2014年9月30日星期二UTC+8下午11时51分14秒，Jens Alfke写道：

[peng liu]

by the way . where is " feature/query" ? i

thanks

在 2014年9月30日星期二UTC+8下午11时51分14秒，Jens Alfke写道：

[peng liu]

sqlite> select *from revs;

1|1|1-2fc730769d3a7ff64c7e3e512cadb12a||0|0|{"contactData":"Tel: +4912342340","progress":75,"type":"contacInfo","userOlder":8,"user_id":"N_older_Pro_0"}|1

2|2|1-554a468df052a997bca3a64a40a9b06d||1|0|{"contactData":"Tel: +4912342341","progress":76,"type":"contacInfo","userOlder":5,"user_id":"N_older_Pro_1"}|1

3|3|1-e550947cda089edd35170c49c6d1023b||1|0|{"contactData":"Tel: +4912342342","progress":65,"type":"contacInfo","userOlder":9,"user_id":"N_older_Pro_2"}|1

4|4|1-0601c2e7054bd8f1c21f26c9ebd93c3a||1|0|{"contactData":"Tel: +4912342343","progress":60,"type":"contacInfo","userOlder":8,"user_id":"N_older_Pro_3"}|1

5|1|2-599f9eb423907d211eb7ca6ebc597ba6|1|1|0|{"contactData":"Tel: +4912342340","progress":75,"type":"contacInfo","userOlder":8,"user_id":"N_older_Pro_0"}|1

6|5|1-f20c5c37c10192ac46cf67e53eb26a70||1|0|{"contactData":"Tel: +4912342343_1","progress":35,"type":"contacInfo","userOlder":1,"user_id":"N_older_Pro_4"}|1

7|6|1-f88596fb5c22efb73407c4aa6b3dd2c3||1|0|{"contactData":"Tel: +4912342343","progress":8,"type":"contacInfo","userOlder":8,"user_id":"N_older_Pro_10"}|1

8|7|1-8f55890cb67557a0f18ba5f852fca07f||1|0|{"contactData":"Tel: +4912342343","progress":8,"type":"contacInfo","userOlder":0,"user_id":"N_older_Pro_11"}|1

and then query it :

(Querying pro:60 older: 2 ,3)

    CBLView * contactInfoView = [self.database viewNamed: @"contactDataByProgressAndOlder"];

    [contactInfoView setMapBlock: MAPBLOCK({

        if ([doc[@"type"] isEqualToString: @"contacInfo"]) {

           if (doc[@"progress"] && doc[@"userOlder"])

             emit(@[doc[@"progress"], doc[@"userOlder"]], doc);

        }

    }) version: @"3"];

    

    //2 - make the query

    CBLQuery* query = [contactInfoView createQuery];

    NSLog(@"Querying pro:%@ older: %@ ,%@",pro, startOlder,endOlder);

    query.startKey = @[pro,startOlder];

    query.endKey = @[[NSNumber numberWithInteger: 75], endOlder];

    return query;

and wrong result:

2014-10-30 21:36:40.068 TestCLDB[851:24414] Querying pro:60 older: 2 ,3

2014-10-30 21:36:40.070 TestCLDB[851:24414] Found document: CBLDocument[N_ol..ro_3] ,{

    "_id" = "N_older_Pro_3";

    "_rev" = "1-0601c2e7054bd8f1c21f26c9ebd93c3a";

    contactData = "Tel: +4912342343";

    progress = 60;

    type = contacInfo;

    userOlder = 8;

    "user_id" = "N_older_Pro_3";

}

2014-10-30 21:36:40.070 TestCLDB[851:24414] Found document: CBLDocument[N_ol..ro_2] ,{

    "_id" = "N_older_Pro_2";

    "_rev" = "1-e550947cda089edd35170c49c6d1023b";

    contactData = "Tel: +4912342342";

    progress = 65;

    type = contacInfo;

    userOlder = 9;

    "user_id" = "N_older_Pro_2";

}

But i query (Querying pro:60 older: 8 ,10) it's run success:

2014-10-30 21:58:40.343 TestCLDB[987:41693] Querying pro:60 older: 8 ,10

2014-10-30 21:58:40.348 TestCLDB[987:41693] Found document: CBLDocument[N_ol..ro_3] ,{

    "_id" = "N_older_Pro_3";

    "_rev" = "1-0601c2e7054bd8f1c21f26c9ebd93c3a";

    contactData = "Tel: +4912342343";

    progress = 60;

    type = contacInfo;

    userOlder = 8;

    "user_id" = "N_older_Pro_3";

}

2014-10-30 21:58:40.348 TestCLDB[987:41693] Found document: CBLDocument[N_ol..ro_2] ,{

    "_id" = "N_older_Pro_2";

    "_rev" = "1-e550947cda089edd35170c49c6d1023b";

    contactData = "Tel: +4912342342";

    progress = 65;

    type = contacInfo;

    userOlder = 9;

    "user_id" = "N_older_Pro_2";

}

2014-10-30 21:58:40.349 TestCLDB[987:41693] Found document: CBLDocument[N_ol..ro_0] ,{

    "_id" = "N_older_Pro_0";

    "_rev" = "2-599f9eb423907d211eb7ca6ebc597ba6";

    contactData = "Tel: +4912342340";

    progress = 75;

    type = contacInfo;

    userOlder = 8;

    "user_id" = "N_older_Pro_0";

}

so ,what's wrong ??? 

query range wrong or why ?

在 2014年9月30日星期二UTC+8下午11时51分14秒，Jens Alfke写道：

[Adam Fraser]

On Thursday, October 30, 2014 12:20:32 AM UTC-7, peng liu wrote:
by the way . where is " feature/query" ? i
thanks

You can find the feature/query branch here: https://github.com/couchbase/couchbase-lite-ios/tree/feature/query

 As you said.it's not support two keys to create a range.and i can filter a first keys ,and then get the first  result  then filter second keys .it same work.
but if the first range not get any result,at this time how to filter second keys ?
so .i do not think it's a good idea.
thanks

Can you clarify the use case here?  If you don't find any rows that meet the criteria for the primary key, why would you need to worry about the secondary keys?

Your examples look like they are still trying to use two keys to create a range, which as Jens describes above can't be done with just an index lookup.

Thanks,

Adam

[Jens Alfke]

On Oct 29, 2014, at 11:58 PM, peng liu <liup...@gmail.com> wrote:

As you said.it's not support two keys to create a range.and i can filter a first keys ,and then get the first  result  then filter second keys .it same work.

but if the first range not get any result,at this time how to filter second keys ?

so .i do not think it's a good idea.

As far as I can tell, you're trying to query a range on the secondary key without the primary key, i.e. all rows in a range of "userOlder" values ignoring the "progress". You can't query a secondary key that way. If you want that query, you'll need another view to do it, one where "userOlder" is the primary (or only) key.

—Jens

[peng liu]

But i need query some columns often (query (A > x and B = y ) or C < w)). as you said. so ,couchbase maybe not a good idea about my project i think.

在 2014年10月31日星期五UTC+8上午1时35分42秒，Adam Fraser写道：

[peng liu]

i know this. but i really need do like this. if i query first key and query second key ,and then filter the results to my final result. it will so slowly if my data large. so ,may be the couch base can not do like this .because i query some like this (query (A > x and B = y ) or C < w)) often. so only sql may be do this better  that i think.

But sql will be sync by myself between android and iOS or wp.. the data will be large. this is the only reason that i use couch base.

thanks.

在 2014年10月31日星期五UTC+8上午2时32分37秒，Jens Alfke写道：

[Jens Alfke]

On Oct 30, 2014, at 6:30 PM, peng liu <liup...@gmail.com> wrote:

i know this. but i really need do like this. if i query first key and query second key ,and then filter the results to my final result. it will so slowly if my data large. so ,may be the couch base can not do like this .because i query some like this (query (A > x and B = y ) or C < w)) often. so only sql may be do this better  that i think.

A SQL database will do exactly the same thing with a query like that — it'll run two or more index lookups, collect the results in memory, and then merge them. The query planner is hiding the details from you, so it looks simple.

But sql will be sync by myself between android and iOS or wp.. the data will be large. this is the only reason that i use couch base.

Data size isn't the only issue. Sync is really hard to do right. Trust me, I've been working on it for three years, and I already had a successful design (CouchDB) to copy :)

—Jens