translate this mysql into a couchdb view?

Question

I'm quite new with couchdb and I want to create a view based on a simple mysql statement. I found this documentation: http://guide.couchdb.org/draft/cookbook.html but there are sadly not all use cases included.

My MySQL-Statement:

SELECT `title`, `id`, `author`, `date`, `text` FROM `news` WHERE `date`<=NOW() AND `author`='22' ORDER BY `date` DESC LIMIT 20,10;

Thank you very much!

Anand Chitipothu · Accepted Answer · 2010-11-14T11:37:39.747

1

You need to write a view with the following map function.

function(doc) {
    emit([doc.author, doc.date], {
            "title": doc.title, 
            "author": doc.author, 
            "date": doc.date, 
            "text": doc.text});
}

Now you can query the view using the following URL:

http://127.0.0.1:5984/dbname/_design/design_doc_name/_view/viewname?startkey=[22, "2010-11-12T10:20:30"]&endkey=[22, {}]&descending=true&skip=20&limit=10

The date in the start key must be the current datetime. There is no way to emulate NOW() in couchdb.

A view in couchdb is just a list of key-value pairs sorted by key and it provides a way to access a range of that list. You need to design your view such that you can get the results that you need using a range query.

edited Nov 14 '10 at 11:37

answered Nov 12 '10 at 12:30

Anand Chitipothu

4,167
4
24
26

As an interesting point here is that you implemented `LIMIT 20`, and not `LIMIT 20,10`. – Victor Nicollet Nov 12 '10 at 17:54
Thank you for your response, but how is possible to make this offset at the limit? – heuri Nov 12 '10 at 18:44
You can't (at least, not with reasonable performance). http://stackoverflow.com/questions/312163/pagination-in-couchdb – Victor Nicollet Nov 12 '10 at 20:36
heuri, you can pass `skip=20`. Corrected the url in my response. – Anand Chitipothu Nov 14 '10 at 11:39

translate this mysql into a couchdb view?

1 Answers1