How to write a view in couchbase for this sql statement

Question

Let's say I have the following documents

Document 1
{
  companyId: "1",
  salesDate: "1425254400000" //this is UTC time as a long
}
Document 2
{
  companyId: "1",
  salesDate: "1425340800000" //this is UTC time as a long
}
Document 3
{
  companyId: "2",
  salesDate: "1425254400000" //this is UTC time as a long
}

I currently have my view set up as

function(doc, meta) { emit([doc.salesDate, doc.companyId], doc); }

Which is pulling back all 3 documents when using

?startkey=[1425254400000,"1"]&endkey=[1425340800000,"1"]

I'm not sure how to make it only pull back the sales for that date range by company id.

The sql version would be SELECT * FROM sales WHERE companyId = :companyId AND salesDate BETWEEN :rangeStart AND :rangeEnd

EDIT: I'm using the rest API.

Answer 1

When designing views for range queries with multiple query fields, the fixed query field(companyId) should be a prefix of the compound index and the range query field should be at the end. With the current view, Couchbase will emit every document where salesDate is within the range without considering companyId.

Reversing the order of keys will work:

function(doc, meta) { 
  emit([doc.companyId, doc.salesDate], doc); 
}

Query:

?startkey=["1", 1425254400000]&endkey=["1", 1425340800000]

N.B. if salesDate is a string and not a numeric value, Couchbase will use lexicographic ordering to perform the range query.