Simple SQL query to Couchbase

I have the following document:

{
   "postId": "Message_2D73B43390041E868694A85A65E47A09D50F019C180E93BAACC454488F67A411_1375457942227",
   "userId": "User_2D73B43390041E868694A85A65E47A09D50F019C180E93BAACC454488F67A411",
   "post_message": "test",
   "attachments": {
       "images": [
       ],
       "audio": [
       ],
       "videos": [
       ]
   },
   "timestamp": 1375457942227,
   "followers": [
   ],
   "follow": 0,
   "reporters": [
   ],
   "report": 0,
   "rerayz": 0,
   "mtype": "post"
}

I would like do the following query:

SELECT * FROM posts WHERE users in ("User_1", "User_2", "User_3") ORDER_BY timestamp LIMIT 20

I did the following and I pass multiple ?keys=["User_1", "User_2", etc] . But how can I get the results sorted by timestamp in order to get only the 20 first?

function (doc, meta) {
  if(doc.mtype == "post") {
    emit(doc.userId, doc.post_message);
  }
}

Any suggestions?

Thanks.

2 Answers

« Back to question.

Hello,

You can only sort by the key of the index so in this case you cannot using a single call get entries for a specific key (user), and sort by timestamp.

If you want do to that you have to either:
- sort them in your application
- create a document based on the result of the query and another index with the timestamp as key.

Regards
Tug
@tgrall

« Back to question.

http://www.slideshare.net/Couchbase/couchbase-20-indexing-and-querying
Here contains some query patterns.

And it's example, how you can get first 20 items from your view query.

ViewResponse response = client.query(view, query);
int cnt = 0;
for (ViewRow row : response) {
  System.out.println(row.getDocument());
   cnt++;
   if (cnt == 20) break;
}