I’m trying to get an app working with Couchbase Lite where the user can heavily filter the dataset that I’m syncing through the Sync Gateway.
There are three different kinds of meta information (categories) on each entry in the DB:
Language (around 20 languages),
Mediatype (around 20 media types), and
Practice area (around 40 practice areas).
The users can filter so they only see a subset of the DB entries when browsing or when using the full text search.
In SQL I would build a complex WHERE clause, like this:
SELECT * FROM entries
WHERE ( (Language = ‘EN’ OR Language = ‘FR’ OR Language = ‘DE’)
AND (Mediatype = ‘Advert’ OR Mediatype = ‘Manual’)
AND (PracticeArea = ‘ABC’ OR PracticeArea = ‘DEF’ OR PracticeArea = ‘XYZ’) )
First question:
How would you get something like this working with CBL?
Second (extended) question:
I also want to do a full text search that filters by additionally checking that the full text contains some keywords. In SQL it might be something like this:
SELECT * FROM entries
WHERE ( (Language = ‘EN’ OR Language = ‘FR’ OR Language = ‘DE’)
AND (Mediatype = ‘Advert’ OR Mediatype = ‘Manual’)
AND (PracticeArea = ‘ABC’ OR PracticeArea = ‘DEF’ OR PracticeArea = ‘XYZ’)
AND (Fulltext CONTAINS “keywords” ) )
Queries this complex will have to be assembled out of multiple map/reduce queries since CBL doesn’t have a query planner as fancy as SQL’s. In your case each of the three main AND clauses can be converted into one query, then you’ll need to do a set-intersection of the resulting documents.
Taking “(Language = ‘EN’ OR Language = ‘FR’ OR Language = ‘DE’)” as an example — you’d create a view with a map function that calls emit(doc[“Language”], null). When querying you’d ignore startKey and endKey and instead set keys to [“EN”, “FR”, “DE”]. Then enumerate the resulting rows and collect the document IDs in a set.
Do the same for the other two queries; then intersect the three sets of document IDs. Those are the documents for the resulting query.
If you’re on iOS you might want to look at the Core Data adapter class; by writing your app code using Core Data APIs you’ll be able to use complex NSPredicates in fetch requests and the adapter will essentially do this kind of work for you. In the next major release we’d like to pull that logic out into the main library so you can do complex queries more easily.
Thanks so far, Jens! Now I’m getting the CBLQueryEnumerators from my different view queries and the next step is to compute the intersection. What do you suggest? Should I go through the enumerators, collect all the document IDs in NSMutableSets and then intersect those using the iOS API, or is there some feature built into CBL to make easier?