Question: searching by id (startkey, endkey)

[acutetech]

Please excuse newbie question:

When I was using PouchDB with Cordova I could retrieve all records that matched certain id values, using values for startkey and endkey, as described here: https://pouchdb.com/api.html#batch_fetch

What is the equivalent with sync-android?

At present I am reading ALL the documents using: List all = this.mDocumentStore.database().read(0, nDocs, true); then doing my own filtering, like this:

for (DocumentRevision rev : all) { if (rev.getId().substring(0, 17).equals(address2)) {...} }

This does not seem very efficient. Do I have to use the query API? If so, could you possibly provide a code snippet that would deliver documents based on id values?

Thanks.

[tomblench]

@acutetech see https://github.com/cloudant/sync-android/blob/master/doc/query.md for comprehensive documentation.

To query on _id > "abc" and _id < "xyz", see the following snippet. Note that these operators will follow SQLite's internal collation order, which I think (?) is Unicode.

    public void testIt() throws Exception {
        DocumentStore ds = DocumentStore.getInstance(new File("/tmp/blah"));
        DocumentRevision dr1 = new DocumentRevision("abc");
        dr1.setBody(DocumentBodyFactory.create("{\"message\":\"hello world\"}".getBytes()));
        DocumentRevision dr2 = new DocumentRevision("cde");
        dr2.setBody(DocumentBodyFactory.create("{\"message\":\"goodbye world\"}".getBytes()));
        DocumentRevision dr3 = new DocumentRevision("xyz");
        dr3.setBody(DocumentBodyFactory.create("{\"message\":\"hello universe\"}".getBytes()));
        ds.database().create(dr1);
        ds.database().create(dr2);
        ds.database().create(dr3);
        ArrayList<FieldSort> fields = new ArrayList<FieldSort>();
        fields.add(new FieldSort("message"));
        ds.query().createJsonIndex(fields, "index1");
        Map<String, Object> gtAbc = new HashMap<String, Object>();
        gtAbc.put("$gt", "abc");
        Map<String, Object> ltXyz = new HashMap<String, Object>();
        ltXyz.put("$lt", "xyz");
        Map<String, Object> idGtAbc = new HashMap<String, Object>();
        idGtAbc.put("_id", gtAbc);
        Map<String, Object> idLtXyz = new HashMap<String, Object>();
        idLtXyz.put("_id", ltXyz);
        Map<String, Object> query = new HashMap<String, Object>();
        query.put("$and", Arrays.<Object>asList(idGtAbc, idLtXyz));
        for (DocumentRevision dr : ds.query().find(query)) {
            System.out.println(new String(dr.getBody().asBytes()));
        }
    }

The above should print {"message":"goodbye world"}

The code to build up the query is a bit laborious. If you are on Java 9 or later you can use a convenience method like Map.ofEntries.

[acutetech]

Thanks for the quick and helpful response. I have modified it for my purpose and attach the code below in case a second example is helpful for others. It seems to work OK. If you like you could comment on (a) lack of indexing and (b) the use of '\uffff' to match "all" ids in the range (taken from the PouchDb documentation).

Here my ids are of the form "80_16_B7_11_22_00_2025dbf6" where the first part is a Bluetooth device address and the second part is a timestamp. So this method searches for all documents from one Bluetooth device (matches the "80_16_B7_11_22_00" in the id).

`

public ArrayList<MedRecord> allMedRecordsFor(String address) throws DocumentStoreException {
    ArrayList<MedRecord> mmmDatabaseDocuments = new ArrayList<MedRecord>();

    String addressWithinId = address.replace(":", "_");

    Map<String, Object> startkey = new HashMap<>();
    startkey.put("$gte", addressWithinId);   // Matches "80_16_B7_11_22_00"

    Map<String, Object> endkey = new HashMap<>();
    endkey.put("$lte", addressWithinId + '\uffff');  // Matches "80_16_B7_11_22_00_andAnyFurtherCharacters"

    Map<String, Object> idStart = new HashMap<>();
    idStart.put("_id", startkey);

    Map<String, Object> idEnd = new HashMap<>();
    idEnd.put("_id", endkey);

    // Create a query which is "idStart AND idEnd", or "_id>=addressWithinId AND _id<=addressWithinId + '\uffff'"
    Map<String, Object> query = new HashMap<>();
    query.put("$and", Arrays.<Object>asList(idStart, idEnd));

    for (DocumentRevision rev : mDocumentStore.query().find(query)) {
        Log.d(TAG, (new String(rev.getBody().asBytes())));
        MedRecord record = MedRecord.fromRevision(rev);
        if (record != null) {
            mmmDatabaseDocuments.add(record);
        }
    }
    return mmmDatabaseDocuments;
}

`

[tomblench]

I would recommend that you don't concatenate the Bluetooth MAC ID and the timestamp, but instead store them in separate fields in your documents. This way you can more easily query for [pseudocode] MAC ID == "blah" and timestamp < x and timestamp > y.

Unless you have legacy reasons to do so, you don't have to choose your own values for _id. If you create documents (either locally on sync-android, or remotely on Cloudant/couchdb), a unique _id will be generated for you.

[acutetech]

Thanks for your tips Tom. In fact, (a) it is (for now) now a legacy issue, and (b) I chose to put information in the id precisiely because PouchDb made it easy to search by IDs, which looked a lot easier than mastering CouchDB Query syntax. When I (newbie) made that decision it was informed by some posts suggesting this was a good idea (here is one I have just found: https://davidcaylor.com/2012/05/26/can-i-see-your-id-please-the-importance-of-couchdb-record-ids/).

I am prepared to accept that is not good practice....