CODEWITHSUNDEEP
couchdbelasticsearchcouchbase
Michael
Michael

Reputation: 1251

Couchbase AND possible?

Via Couchbase views or any other method available in couchbase is it possible to retrieve a result set that would be the equivalent to a SQL query of WHERE AND. For example take the following SQL query. The date field would be a timestamp: 

SELECT * FROM items WHERE category = 'Trips' AND visible = 'EVERYONE' ORDER BY date

It appears that with views, only the first field of a complex key with the use of startkey and endkey could actually limit to a category. For example with the below view a complex key passed with the first value as Trips for the start and end key would limit the result set to just the Trips items. Only problem being the following keys would only be sorting the data. is this correct or am I missing something?

function (doc, meta) 
{
  if(meta.type == "json" && doc.type == "POST") 
  {
    emit([doc.category, dateToArray(doc.created), doc.visibility], null);
  }
}

Is it possible to do the above SQL query in Couchbase that would return a result set where the only results have met the two conditions?

I do know of the elastic search plugin. Would this provide the functionality I'm after? But would it come at the cost another cluster of servers in a production environment and results not being readily available because of indexing?

Upvotes: 2

Views: 154

Answers (1)

m03geek
m03geek

Reputation: 2538

If you need exactly WHERE AND (just like in your sql example) try:

function (doc, meta) 
{
  if(meta.type == "json" && doc.type == "POST") 
  {
    emit([doc.category, doc.visibility, dateToArray(doc.created)], null);
  }
}

startkey = ['Trips', 'EVERYONE']; endkey = ['Trips', 'EVERYONE', {}]

Ordering parameters in complex key is very important. You need to put all "constants" first, because date of those docs will be varying, and everything that you place after date in complex key will be ignored. Look through this thread, and you'll find answer about how couchbase index works.

So, if you need exact match by one or more fields, you can do it with couchbase. If you need filter your results by multiple ranges (i.e. doc.price = 100...200 and doc.category = 1..5) you need 1) multiple views and app-side merging (bad way) or 2) elasticsearch plugin (better way)

Upvotes: 2

Related Questions