Couchbase query based on time

Question

I've been trying to figure out how to get data back based on a timestamp. Basically I want to query 100 docs based on a timestamp field. the 100 docs should be older than the timestamp I passed it. Also I want to create a refresh where I can pass a timestamp and I get 100 newer docs. Obviously logic would be different here, but having difficulty figuring out how Couchbase can accomplish this.

Here is what I have so far:

My view, as you can see I also need to use a complex key because I'm not only checking date but my visibility field. I do not having anything in the reduce part of the view.

 function (doc, meta) 
{
  if(meta.type == "json" && doc.type == "POST" && doc.created != null) 
  {
    emit([dateToArray(doc.created), doc.visibility], null);
  }
}

I am querying using the java client, limit is set to 2 for testing. What is strange here is that setDescending has to be false to get anything back. Also it doesn't matter which date I pass in I always get a result. What I want as expected behaviour is to pass a date and only get results equal or older than the date.

 View view = client.getView("dev_posts", "post_list");
  ComplexKey keys = ComplexKey.of(DataConstants.getDateAsArray(startDate), postType);

  Query query = new Query();
  query.setRangeStart(keys);
  query.setIncludeDocs(true);
  query.setLimit(2);
  query.setDescending(false);

  ViewResponse response = client.query(view, query);

EDIT:

Essentially what I'm looking for from Couchbase is similar to Facebook, Pintrest etc. mobile apps. On a given timestamp when a user refreshes I want to get anything newer. When a user is scroll I want to get the next group past a particular date.

** Update **

So this has been solved, but going to investigate it further. The ComplexKey is turning our date array into "[2013,11,8,20,0,0]" instead of leaving it [2013,11,8,20,0,0]. Going to investigate it further. Work around for now was not to use to ComplexKey but create our own complex key and pass it to startKey as just a Key.

score 1 · Answer 1 · answered Nov 10 '13 at 20:18

1

You need to reorder and emit your compound key like so:

function (doc, meta) {
  if(meta.type == "json" {
    if(doc.type == "POST") {
      emit([doc.visibility,dateToArray(doc.created)], null);    
    }
  }
}

Which will produce keys like: [true,[2013,11,8,20,0,0]]

Pass in a startKey and endKey in the format like above and that will filter all documents since the startKey date that are either doc.visibility False or True (depending on your start key), I assume you only want doc.visibility == true.

Your return keys will be ordered by the first part of the compound key, so the first result in the ViewResult will actually be the oldest document returned. So you will have to reverse the array in your application logic if you want the most recent document first.

Remember to take into consideration the stale parameter if you are building a 'live' feed.

answered Nov 10 '13 at 20:18

scalabilitysolved

2,473
1
26
31

There are more than two conditions for visibility, its not just a true or false – Michael Nov 11 '13 at 17:51
ok so just construct your key like [visibility,[2013,11,8,20,0,0]], that'd work for you wouldn't it? – scalabilitysolved Nov 11 '13 at 17:52
Actually solved it, the complex key was turning [2013,11,8,20,0,0] into "[2013,11,8,20,0,0]". We want the sorting to date first. – Michael Nov 11 '13 at 19:28
@Michael - Could you please post your solution? I tried with date first but was unable to make it work with your example, thats why I had suggested the reverse compound key. – scalabilitysolved Nov 11 '13 at 19:48
Basically the key needs to look like this: startkey=[[2013,11,10,23,12,0],"EVERYONE"]&descending=true , I found ComplexKey in the java client was converting it to startkey=["[2013,11,10,23,12,0]","EVERYONE"]&descending=true basically we created our own key to match this: [[2013,11,10,23,12,0],"EVERYONE"] for the view posted above. – Michael Nov 11 '13 at 19:57

score 0 · Answer 2 · answered Nov 08 '13 at 07:58

0

You can use the system of reference documents with two level of hierarchy. On first level documents, those id is timestamp (Ref::). They should contain ids of you finally documents. On second level document, that contains ids of all ref. documents from first level. If the number of reference documents will be too large, you can use third level of referencing. If something is not entirely clear, tell me, I'll try to explain it differently.

P.S. Also you can use ElasticSearch. This will give you the opportunity to perform any request without creating a hierarchy of reference documents, but perhaps a problem with memory and delayed replication.

answered Nov 08 '13 at 07:58

Vladislav Koroteev

538
6
21

Thanks for the suggestion. I do think in this instance reference documents wouldn't be the correct pattern. Really both condition need to be met, so the time needs to be below or above the given timestamp and the visibility field will be the same for all documents. Reference docs on this data would create far too many extra docs in the system. I have created views using complex keys that have worked quite well, but the issue seems to be the range with time involved. It may be I need to look at the reduce part of the view. – Michael Nov 08 '13 at 16:07
1

I found [something](http://www.couchbase.com/communities/q-and-a/how-write-query-specific-range-and-group-clause) that did not know before, maybe it will be useful for you. "the range query is reading the full index and we read the startkey from left to right only (as a full key)". Maybe after concatenating 2 keys you always get result. Have you test your view query without "doc.visibility" as part of ComplexKey? – Vladislav Koroteev Nov 09 '13 at 11:59

Couchbase query based on time

2 Answers2