CouchDb filter and sort in one view

Question

I'm new to the CouchDb. I have to filter records by date (date must be between two values) and to sort the data by the name or by the date etc (it depends on user's selection in the table). In MySQL it looks like

SELECT * FROM table WHERE date > "2015-01-01" AND date < "2015-08-01" ORDER BY name/date/email ASC/DESC

I can't figure out if I can use one view for all these issues. Here is my map example:

function(doc) {
    emit(
        [doc.date, doc.name, doc.email],
        {
            email:doc.email,
            name:doc.name,
            date:doc.date,
        }
    );
}

I try to filter data using startkey and endkey, but I'm not sure how to sort data in this way:

startkey=["2015-01-01"]&endkey=["2015-08-01"]

Can I use one view? Or I have to create some views with keys order depending on my current order field: [doc.date, doc.name, doc.email], [doc.name, doc.date, doc.email] etc?

Thanks for your help!

Answer 1

you COULD use a list function for that, in two ways:

1.) Couch-View is ordered by dates and you sort by e-amil => but pls. be aware that you'd have to have ALL items in memory to do this sort by e-mail (i.e. you can do this only when your result set is small)

2.) Couch-View is ordered by e-mail and a list function drops all outside the date range (you can only do that when the overall list is small - so this one is most probably bad)

possibly #1 can help you

Answer 2

As Sebastian said you need to use a list function to do this in Couch.

If you think about it, this is what MySQL is doing. Its query optimizer will pick an index into your table, it will scan a range from that index, load what it needs into memory, and execute query logic.

In Couch the view is your B-tree index, and a list function can implement whatever logic you need. It can be used to spit out HTML instead of JSON, but it can also be used to filter/sort the output of your view, and still spit out JSON in the end. It might not scale very well to millions of documents, but MySQL might not either.

So your options are the ones Sebastian highlighted:

1. view sorts by date, query selects date range and list function loads everything into memory and sorts by email/etc.

2. views sort by email/etc, list function filters out everything outside the date range.

Which one you choose depends on your data and architecture.

With option 1 you may skip the list function entirely: get all the necessary data from the view in one go (with include_docs), and sort client side. This is how you'll typically use Couch.

If you need this done server side, you'll need your list function to load every matching document into an array, and then sort it and JSON serialize it. This obviously falls into pieces if there are soo many matching documents that they don't even fit into memory or take to long to sort.

Option 2 scans through preordered documents and only sends those matching the dates. Done right this avoids loading everything into memory. OTOH it might scan way too many documents, trashing your disk IO.

If the date range is "very discriminating" (few documents pass the test) option 1 works best; otherwise (most documents pass) option 2 can be better. Remember that in the time it takes to load a useless document from disk (option 2), you can sort tens of documents in memory, as long as they fit in memory (option 1). Also, the more indexes, the more disk space is used and the more writes are slowed down.