CODEWITHSUNDEEP
javascriptnode.jssortingnosqlcouchdb
Super Hans
Super Hans

Reputation: 128

Complex Key - Filter by first key and sort only by second key

I have an application that displays services upon which I would like the user to be able to filter by service name while always being sorted (descending) by date (no sorting on service name).

Also the following question doesn't contain the appropriate solution:

Couch DB filter by key and sort by another field

Below is a simplistic version of the code to give an idea of what I am currently trying (serviceName must be first key as I cannot filter exclusively with the second key):

function(doc) { 
    if(doc.type && doc.type=="incident"){
        emit([doc.serviceName, doc.date], doc)
    }
 };

Here is a Snippet where from my API writen in Node, that builds the parameters for the query:

if (req.query.search !== "") {
    const search = req.query.search;
    params = { 
        limit: limit, 
        skip: skip, 
        startkey: [search+"Z"], 
        endkey: [search], 
        descending:true, 
        include_docs: true 
    };
} else {
    params = { limit: limit, skip: skip, descending:true, include_docs: true };

The above code currently filters service name but also sorts by service name before sorting by date. Is there anything I can add like (check snippet below) to force the query to sort the results by date without me having to do it in the code after I get the result.

const sort = [{'date': 'desc'}];

What I want is something like this when filtering by servicename:

SELECT * FROM incidents
    WHERE serviceName LIKE @search+'%'
    ORDER BY date DESC

But when not filtering by servicename:

SELECT * FROM incidents
ORDER BY date DESC

Upvotes: 0

Views: 218

Answers (1)

Glynn Bird
Glynn Bird

Reputation: 5637

One way to do this is to ensure that the second element of the array you emit (the date-related bit) goes down as time proceeds. This can be achieved with a bit of hacking in the map function

function(doc) {
  if(doc.type && doc.type=="incident") {

     // get the date from the document
     var d = doc.date;

     // remove dash characters
     var d1 = d.replace(/\-/g,'');

     // convert to integer
     var d2 = parseInt(d1)

     // Subtract from number representing the year 3000
     emit([doc.serviceName, 30000000 - d2], doc)
  }
}

You may now query this view in ascending order (without descending=true) and the data will be sorted by serviceName and the time (reversed).

Upvotes: 2

Related Questions