How to get "rev" id on N1QL query?


#1

Hi,

Im communicating with SG of Couchbase for my web admin through N1QL query. i want rev id for updating document with new changes.

Here is my query.

select documentType ,accountNumber,storeNumber,storeAccountNumber,channels,createdDate,updatedDate, META(d).id from default d where accountNumber = '123451' AND documentType = 'StoreModel'

My StoreModel documents look like below.


#2

the “_rev”:“number-md5(hash)” for Sync gateway docs is different from CB META() . Since SG stores the older revision. just update the document via SG and you’ll see the new 2-(md5hash).


#3

@househippo Thanks for quick reply. yes i will update document by SG. but Im list out all docs related query by using N1QL query. so first i’m fetching records By N1QL and then i’m updating records by SG, while updating i need to pass _rev. so how can i achieve this process.


#4

oh ok … so do a query like

SELECT META().id as SGKey , _sync.history.revs[0] as REV , … FROM bucket WHERE accountNumber = ‘123451’ AND documentType = ‘StoreModel’ AND _sync IS NOT MISSING;


#5

@househippo Thanks its working perfect. How can i apply index concept here for get data quickly. how to do pagination on N1QL queries.


#6

in the above which one do you have more unique and smaller grouping of ?


#7

@househippo Actually documentType is more unique and then accountNumber.


#8

:+1: … do it up with making INDEXES and test the response time of both. You can force a N1QL statement to use particular index via

SELECT META().id as SGKey , _sync.history.revs[0] as REV , … FROM bucket
USE INDEX (‘index_name_here’)
WHERE accountNumber = '123451’
AND
documentType = 'StoreModel’
AND _sync IS NOT MISSING;

Source: http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/hints.html


#9

:slight_smile: Thanks, hey what about Pagination on N1QL. Really Thanks for your glad help.


#10

How are we so sure that _sync.history.revs[0] will always give us the latest revision id? I mean it is always required to provide the latest revision id. So, as far as I understand won’t following be the correct selection?

_sync.history.revs[ARRAY_LENGTH(_sync.history.revs) - 1] AS REV


#11

You are correct… can do this

SELECT _sync._rev as REV ..... FROM ....

this gives you latest & winning branch rev.


#13

Yes that is, thanks! :slight_smile: