How do I create a dynamic query using pouchdb (couchdb probably the same thing)?

Question

I have a dynamic query that I'd like to write using PouchDB, but I am finding it difficult to translate how I might achieve this using an SQL database compared to PouchDB.

I have number of documents of type 'session' in PouchDB, and there needs to be a complex search for this type of document. I'd like to filter on things like title (strings that start with a provided value), status, the owner, equipment id, a start date, an end date, and a few other things.

Now, I know I could emit an array key, something like:

            document.views = {
                by_title_status_owner: {
                    map: function(document) {
                        if(document._id.startsWith('session')) {
                            emit([
                                document.title.toUpperCase(),
                                document.status.text.toUpperCase(),
                                document.owner.refId
                            ], null);
                        }
                    }.toString()
                }
            };

My problem is that I have no idea how to make a query using startkey and endkey. I am trying stuff like this:

                startkey: [
                    (filters.title || '').toUpperCase(),
                    (filters.status || '').toUpperCase(),
                    (filters.owner || '')
                ],
                endkey: [
                    (filters.title || '').toUpperCase() + '\uffff',
                    (filters.status || '').toUpperCase() + '\uffff',
                    (filters.owner || '').toUpperCase() + '\uffff'
                ],

But it only seems to filter on the first index - meaning it's correctly filtering on the title, but when searching by the status or owner, the query returns all of the results.

I think the problem is that I really want to provide different emit array keys and provide different startkey/endkey combinations based on what the user has inputted, but this seems extremely hard to do with PouchDB.

For example, let's say the user typed in 'Lig' for their title search and selected CLOSED for status. That would mean the startkey should look like ['LIG', 'CLOSED'] and the endkey might look like ['LIG\uffff', 'CLOSED']. But does this mean I have to emit every single permutation of key indexes to match this dynamic startkey/endkey?

Here is my first attempt at this, and I haven't even added the 2+ parameter emited keys.....

        getAllByCriteria: function(filters) {
            var startkey = [], endkey = [];

            if(filters.title) {
                startkey.push((filters.title || '').toUpperCase());
                endkey.push((filters.title || '').toUpperCase() + '\uffff');
            }

            if(filters.status) {
                startkey.push((filters.status).toUpperCase());
                endkey.push((filters.status).toUpperCase());
            }

            if(filters.owner) {
                startkey.push(filters.owner);
                endkey.push(filters.owner);
            }

            return Database.instance().query('session_indexes/by_criteria', {
                startkey: startkey,
                endkey: endkey,
                include_docs: true
            }).then(function(result) {
                return _(result.rows).map(function(row) {
                    return Session.fromDocument(row.doc, new Session());
                });
            });
        },

and:

        db.upsert('_design/session_indexes', function(document) {
            document.views = {
                by_criteria: {
                    map: function(document) {
                        if(document._id.startsWith('session')) {
                            emit([], null)
                            emit([document.title.toUpperCase()], null);
                            emit([document.status.text.toUpperCase()], null);
                            emit([document.owner.refId], null);
                        }
                    }.toString()
                }
            };

            return document;
        });

I'm really confused. I'd appreciate any kind of help.

Thanks!

ermouth ermouth · Accepted Answer · 2015-04-03T04:50:10

You can use combination of view and list.

Emit keys like ["title", doc.title.toUpperCase()] , ["status", doc.status.toUpperCase()] using map fn, say indexfn.
Create _list fn, say filter that accepts query like {field1:"value1", field2:"value2"...} – lists can receive custom query params unlike map fns. Your filter must drop rows if they do not match. Surely to make filtering possible your _list must receive full (or somehow trimmed) set of fields from view fn.
Perform request like /_design/ddoc/_list/filter/_view/indexfn?startkey=["field1","val1"] & endkey=["field1","val1z"] & filter={"field2":"val2", "field3":"val3"}

This approach is reasonable when your _list receives, say, not more then thousand rows from view – piping data from view through _lists fn to end user is quiet slow.

So if you have more or less alphbetically distributed data set with ~1M records, you can have good performance if you send startkey that is 3 or more chars long.

How do I create a dynamic query using pouchdb (couchdb probably the same thing)?

1 Answers