No index exists for this sort - couchDB

Question

Getting error when accesing the data by using sort method

Error is

No index exists for this sort

Create index code is

db.createIndex({
    index: { fields: ["timestamp", "upVote"] },
}).then(() => {
    this.intitialDatafromDB(db);
});

find function is

db.find({
   selector: { upVote: { $gt: 5 } },
   sort: [{ upVote: "desc" }],
   fields: ["news"],
}).then((result: any) => {
    this.data = result.docs;
}).catch((err: any) => {
    console.log(err);
});

user_4964791 · Answer

The reason your query is failing is that the order of index fields matters.

From the pouchdb documentation Indexing on more than one field

One thing to note is that the order of these fields matters when creating your index

By specifying the index as so

fields: ['timestamp','upVote']

The index looks like this

timestamp	upVote
1590399369500	3
1590399369600	4
1590399369700	1
1590399369700	2
1590399369700	3
1590399369800	1

Note the timestamp 1590399369700, and how the secondary field upVote sorts.

If your index fields were ordered like so

fields: ['upVote','timestamp']

Given the theoretical data above, the index would look like this

upVote	timestamp
1	1590399369700
1	1590399369800
2	1590399369700
3	1590399369500
3	1590399369700
4	1590399369600

and your query would return the results you expect, as is demonstrated in the snippet below. I recommend reading over Map/reduce queries; grasping the concepts presented in that documentation will provide a deeper understanding of why this is so.

const g_result = 'result';
const getEl = id => document.getElementById(id);
let db;

async function view() {
  const view = getEl(g_result);

  const result = await db.find({
    selector: {
      upVote: {
        $gt: 5
      },
    },
    sort: [{
      'upVote': 'desc'
    }],
    fields: ['news','upVote']
  }, );

  view.innerText = JSON.stringify(result, undefined, 3);
}

// canned test documents
function getDocsToInstall() {
  return [{
      timestamp: 1590399369508,
      upVote: 3,
      news: "new item 1"
    },
    {
      timestamp: 1590399248600,
      upVote: 4,
      news: "new item 2"
    },
    {
      timestamp: 1590399248600,
      upVote: 5,
      news: "new item 3"
    },
    {
      timestamp: 1590399248700,
      upVote: 6,
      news: "new item 4"
    },
    {
      timestamp: 1590399248900,
      upVote: 7,
      news: "new item 5"
    },
    {
      timestamp: 1590399249000,
      upVote: 8,
      news: "new item 6"
    },
  ]
}

// init example db instance
async function initDb() {
  db = new PouchDB('test', {
    adapter: 'memory'
  });

  await db.bulkDocs(getDocsToInstall());
  await db.createIndex({
    index: {
      fields: ['upVote', 'timestamp']
    }
  });
};

(async() => {
  await initDb();
  await view();
})();

https: //stackoverflow.com/questions/69122670/no-index-exists-for-this-sort-couchdb#

No index exists for this sort - couchDB

1 Answers