'Cannot sort on field(s) xxx when using the default index

I have the following pouchdb query, I dont see there are any problems here, but it throws error Cannot sort on field(s) "createTime" when using the default index. Any ideas on how to solve this? And do I have to do createIndex each time I make a query??

let getData = () => {
  let re = new RegExp('2017-09-06')
  db.createIndex({
    index: {
      fields: ['createTime'],
    }
  }).then((result)=>{
    return db.find({
      selector: {createTime: {$regex:re}},
      fields: ['createTime'],
      sort: ['createTime']
    }).then(function (result) {
      // handle result
      debugger
    }).catch(function (err) {
      console.log(err)
      debugger
    });
  })

}
pouchdb


Solution 1:[1]

I don't really know why, but adding the selector with 'gt: null' seems to work on my case. I'm using pouchDB v 6.4.1. Can you try these :

db.createIndex({
  index: {
    fields: ['createTime'],
  }
}).then((result) => {
  return db.find({
    selector: {
      $and: [
        { createTime: {'$gt': null} },
        { createTime: {'$regex': new RegExp('2017-09-06')}} }
      ]
    },
    fields: ['createTime'],
    sort: ['createTime']
  }
);

Solution 2:[2]

So, after spending some time trying to resolve this issue, here's a simple solution that worked for me: (Turns out I didn't read the documentation properly and You have to include the field(s) to sort in selector as well)

let sort_field = 'name'; 
let selectorQuery = {
   latest: {$regex: ''},
   oldest: {$regex: ''},
   name: {$regex: ''},
}


clientsDB.createIndex({
   index: {
      fields: ['latest', 'oldest', 'name'] //specify all fields
   }
}).then(() => {
    clientsDB.find({
       selector: selectorQuery,
       sort: [sort_field]
   }).then(res => {
       console.log(res.docs)
   }); 
})

It will sort em accordingly.

Solution 3:[3]

I have been struggling with this error for a long time. One possible and well hidden cause is that you may have multiple fields in your index, but for sort, the order do matter.

Reading the plugin code:

// e.g. ['a'], ['a', 'b'] is true, but ['b'], ['a', 'b'] is false
function oneArrayIsSubArrayOfOther(left, right) {

// check that at least one field in the user's query is represented
// in the index. order matters in the case of sorts
function checkIndexFieldsMatch(indexFields, sortOrder, fields) {

I have not seen that documented anywhere.

Working code:

await db.createIndex({
  index: { fields: ['createdAt', 'kind'] }, // createdAt must be first
});

await db.find({
  selector: { kind: 'foo' },
  sort: ['createdAt']
});

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 RAZ0229
Solution 3 FitzFish

Related Questions