Simple SQL query to Couchbase


#1

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


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


#3

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