CODEWITHSUNDEEP
couchdbpouchdb
Kenny Ki
Kenny Ki

Reputation: 3430

How to query PouchDB with SQL-like operators

Being relatively new to PouchDB/CouchDB, I'm still trying to wrap my head around how to use the map/reduce correctly in different cases.

Supposed that I have document structure like this:

{
  _id: 'record/1',
  labels: {
    // many-to-many relationship
    'label/1': true, // let's assume that this is 'Label A'
    'label/3': true, // 'Label C'
    'label/4': true // 'Label D'
  }
},
{
  _id: 'record/2',
  labels: {
    'label/1': true, // 'Label A'
    'label/2': true // 'Label B'
  }
}

What are the correct ways to define view for db.query function to search:

  1. records with 'Label A' or 'Label B'
  2. records with 'Label A' and 'Label B'

Upvotes: 1

Views: 2156

Answers (3)

eDriven_Levar
eDriven_Levar

Reputation: 396

This is an older post. However, It is possible to use underscore.js to help with some of the queries. It can help pull out the data you want without having to make several trips to the database (unless you want).

Upvotes: 0

Kenny Ki
Kenny Ki

Reputation: 3430

As much as I wanted to use the pouchdb-find plugin, I couldn't find a way to achieve what I needed. Instead I used a workaround:

Change the document structure to store label IDs in an array

{_id: 'record/1', name: 'Record 1', labels: ['label/1', 'label/2', 'label/3']},
// may not be sorted when being stored
{_id: 'record/2', name: 'Record 2', labels: ['label/1', 'label/5', 'label/7', 'label/3']},
{_id: 'record/3', name: 'Record 3', labels: ['label/2', 'label/3', 'label/4', 'label/5']}

Create a design document

It will emit multiple complex keys for each record to represent all possible label-mappings in an ascending order. The map function will utilize a recursive process to generate the keys:

{
  _id: '_design/records-with-labels',
  views: {
    'records-with-labels': {
      map: function(doc) {
        // important: sort them so that the required keys to be created are lesser
        var labelIds = doc.labels.sort();
        var lastIx = labelIds.length - 1;

        var emitKey = function emitKey(currentKey, currentIx) {
          console.log('emitting: ' + currentKey.join(',') + ' for ' + doc._id);
          emit(currentKey, null);

          var nextIx = currentIx + 1;

          for (var jumpIx = nextIx + 1; jumpIx <= lastIx; jumpIx++) {
            var jumpedLabelId = labelIds[jumpIx];
            var jumpingKey = currentKey.concat([jumpedLabelId]);

            console.log('emitting: ' + jumpingKey.join(',') + ' for ' + doc._id);
            emit(jumpingKey, null);
          }

          if (nextIx > lastIx) {
            return;
          }

          var nextLabelId = labelIds[nextIx];

          currentKey.push(nextLabelId);

          emitKey(currentKey, currentIx + 1);
        };

        labelIds.forEach(function(labelId, i) {
          emitKey([labelId], i);
        });

      }.toString()
    }
  }
}

For example, the document record/1 will have these keys generated:

emitting: label/1 for record/1
emitting: label/1,label/3 for record/1
emitting: label/1,label/2 for record/1
emitting: label/1,label/2,label/3 for record/1
emitting: label/2 for record/1
emitting: label/2,label/3 for record/1
emitting: label/3 for record/1

Querying

I just need to ensure that the query labels are sorted in ascending order.

To query records that have 'label/1' and 'label/3':

Db.query('records-with-labels', {
  key: ['label/1', 'label/3']
});

To query records that have 'label/3' or 'label/3':

Db.query('records-with-labels', {
  keys: [['label/1'], ['label/3']]
});

This will give us duplicated records that has both labels, but a reduce function should help eliminating them.

Conclusion

For now I don't know if there is a better solution, but this is good enough for me because in my case, a record will not have too many labels.

If you have better suggestions, please comment or edit the answer.

Upvotes: 0

nlawson
nlawson

Reputation: 11620

There are no OR operations in PouchDB/CouchDB mapreduce queries, so you would have to break it up into two separate queries.

Eventually these kinds of operations will be supported in pouchdb-find, but as of this writing, $or hasn't been implemented yet.

Upvotes: 1

Related Questions