Querying multiple fields at the same time on a view

Couchbase Server
1

Hi,

I have a scenario where-in in my application a user will be searching by search criterias (say field1, field2, field3 (field 3 is a daterange)). Field1 is a mandatory search criteria, but fields 2…5 are optional and they can come as combinations through user input.

So I created a single view that emits multiple keys (for all possible combinations that a user can search on).

eg:

function (doc, meta) {
if(doc.eventType==‘myEvent’ && doc.field1){
if(doc.field2) {
emit([doc.field1,doc.field2]);
}
if(doc.field3) {
emit(doc.field1,doc.field3]);
}
if(doc.field4) {
emit([doc.field1,doc.field4]);
}
if(doc.field5) {
emit([doc.field1,doc.field5]);
}
if(doc.field2 && doc.field3) {
emit([doc.field1,doc.field2,doc.field3]);
}
if(doc.field2 && doc.field4) {
emit([doc.field1,doc.field2,doc.field4]);
}
if(doc.field2 && doc.field5) {
emit([doc.field1,doc.field2,doc.field5]);
}
if(doc.field3 && doc.field4) {
emit([doc.field1,doc.field3,doc.field4]);
}
if(doc.field3 && doc.field5) {
emit([doc.field1,doc.field3,doc.field5]);
}
if(doc.field4 && doc.field5) {
emit([doc.field1,doc.field4,doc.field5]);
}
if(doc.field2 && doc.field3 && doc.field4) {
emit([doc.field2,doc.field3,doc.field4]);
}
if(doc.field2 && doc.field3 && doc.field5) {
emit([doc.field1,doc.field2,doc.field3,doc.field5]);
}
if(doc.field2 && doc.field4 && doc.field5) {
emit([doc.field1,doc.field2,doc.field4,doc.field5]);
}
if(doc.field3 && doc.field4 && doc.field5) {
emit([doc.field1,doc.field3,doc.field4,doc.field5]);
}
if(doc.field2 && doc.field3 && doc.field4 *&& doc.field5) {
emit([doc.field1,doc.field2,doc.field3,doc.field4,doc.field5]);
}
}
}

Is this the correct approach or am I missing something here as the index values generated will be redundant and the index file size grows exponentially.

As I’ am very new to Couchbase and based on my limited knowledge I understand that couch base doesn’t allow to search for empty values in a query? For example, I’ am not able to achieve above using a single emit like:

function (doc, meta) {
if(doc.eventType==‘myEvent’ && doc.field1){
if(doc.field2 && doc.field3 && doc.field4 *&& doc.field5 {
emit([doc.field1,doc.field2,doc.field3,doc.field4,doc.field5]);
}
}
For the above map, the index builds fine.

But the problem is with “querying” this index, say for a scenario wherein I have multiple search criteria as above (i.e. user may provide one of them or a combination of them), the querying is not possible through key/keys. Else the option that I could find was N1QL, which seems to still be in a developer preview state, so I assume it’s not production ready.

Is there any other better approach for doing such a search and retrieve, as it is a very common use case and as I come from a Relational DB world I’ am finding it difficult to get this concept, or I’ am not sure if I’ am missing something here, please help me, what I’am looking for is a SQL query on a single view, something like:

— fields provided in the where clause may have 1 or more values. Or they may be empty. This is purely based on search criteria entered by end user

select * from view_name where doc.field1=“x” and doc.field2 in (“y”,“z”,“d”) and doc.field3 in (“abc”) and Date_column between 2011/02/25 and 2011/02/27 sort by Date_Column desc;
Is such an above query and retrieval possible at all with couch base views and couch base client java api.

Thanks.

2

Hi,

Further to my above post, I was able to reduce the number of emits with a concatenation and positional logic.
i.e for example my map looks like:

emit([String(doc.a)+“B”+String(doc.b)+“C”+String(doc.c)+“D”+String(doc.d)],null)‌ ; emit([String(doc.a)+“C”+String(doc.c)+“B”+String(doc.b)+“D”+String(doc.d)],null)‌ ; emit([String(doc.a)+“D”+String(doc.d)+“C”+String(doc.c)+“B”+String(doc.b)],null)‌ ;

And then my startkey=startkey: ["a”], endkey=[“aBb\ufff0”] - something which gives me results like a ‘like%’ search.

But still I think I may need to do multiple queries to mimic an “in” search, as I’ am not able to use the multi-get and range searches together. And then build up the results in the app layer.

Thanks.