Couchbase Lite - Sorting Map/Reduce by values

Advertissement: I'm using CouchbaseLite Android 1.4.0 and I'm not ready to switch to 2.0.

To get the context of my issue:

I have 2 doc types: PATIENT & VISIT
A patient is linked to a visit by a field visitId
A visit has a startDateTime and a endDateTime
A visit is considered as 'open' if the endDateTime is null or missing
A visit can be of different types through the field visitType
A visit can be inactive through the field inactive

I want to:

Get all patients (id + name) of all open and active visits of a certain type
The result is paginated using skip and limit
Get the result sorted by name

I managed to get all the correct patients and to paginate the result but the issue is with the sorting. I can't make a 'post sorting' (getting the result in a random order and then use self-made method to sort them) because of the pagination.

So here is how it works actually without the sorting:

THE MAP/REDUCE

if (isVisit()) {
    Object inactive = document.get(FIELDS.INACTIVE);
    if(inactive == null)
        inactive = false;

    Document patientDoc = database.getExistingDocument(FIELDS.PATIENT_ID);

    if(patientDoc != null && patientDoc.getProperties() != null) {
        Object[] keys = {
                document.get(CouchbaseVisitManager.FIELDS.STOP_DATE_TIME),
                inactive,
                document.get(CouchbaseVisitManager.FIELDS.VISIT_TYPE)
        };
        Object[] values = {
            document.get(FIELDS.PATIENT_ID),
            patientDoc.getProperties().get(FIELDS.FAMILY_NAME),
            patientDoc.getProperties().get(FIELDS.FIRST_NAME)
        }
        emitter.emit(keys, values);
    }
}

THE QUERY

Query query = getQuery(Views.PATIENTS_CURRENTLY_IN_VISIT); //Helper method to create a query
query.setKeys(new ArrayList<Object>(){{
    add(new ArrayList<Object>(){{
        add(null);
        add(false);
        add(visitType);
    }});
}});
query.setSkip(skip);
query.setLimit(limit);

And this is working well to get my patients but not for sorting them.

I tried to add patient names inside the view like this:

Object[] keys = {
    patientDoc.getProperties().get(FIELDS.FAMILY_NAME),
    patientDoc.getProperties().get(FIELDS.FIRST_NAME),
    document.get(FIELDS.STOP_DATE_TIME),
    inactive,
    document.get(FIELDS.VISIT_TYPE)
};

And update my query.keys like this:

query.setStartKey(new ArrayList<Object>(){{
    add(null);
    add(null);
    add(null);
    add(false);
    add(visitType);
}});
query.setEndKey(new ArrayList<Object>(){{
    add(new HashMap<>());
    add(new HashMap<>());
    add(null);
    add(false);
    add(visitType);
}});

But this returns no patients (result.size = 0)

So... I don't know how to achieve my goal. I tought of a way to sort by values but it doesn't seem to exist yet (should wait for 2.0 I think). But is their any workaround to achieve this kind of behavior?

Thank's for reading.

Solution

View results are always sorted by key. So you should add the patient name to the key and then you'll get your sorting.

Be careful to put the name into the correct place of the keys array since the sorting is lexicographical, that is if the name is going to be the last component then you'll get all open inactive sorted by name, then all open active sorted by name e.t.c.

In case you need to put your name key in front of the other keys but still be able to filter your results, then you will need to properly construct the start key and the end key so that the full range of every possible patient names would be accepted while the later keys would be filtered. You will have to provide some values which will definitely be less than or greater than any possible patient name.

Following the key collation defined for Couchbase views, a null will be always less than any string, and an empty map {} can be used to match past the last string. You are basically doing it right in your last sample. Don't forget to switch the start and end keys logic if you would like to query the view in reverse mode

Another thing to take care of is always incrementing the version of the view after changing its logic or key set, otherwise you will continue getting old results. Incrementing the version will force a refresh of the view, executing the updated logic in the view function over all your data.