Mechanism of keys in view for PREPARE/EXECUTE statement

n1ql

#1

I can use keys to assign multi-key to query data within one query.
Can PREPARE/EXECUTE statement provide the similar feature?


#2

PREPARE / EXECUTE can do everything you can do with a regular SELECT / UPDATE / DELETE / other statement.


#3

no, for example, I have a PREPARE statement:

curl -v http://127.0.0.1:8093/query/service -d 'statement=PREPARE getResByKey1AndKey2 FROM SELECT  a.value from mydb a where a.key1 == $key1 and a.key2 == $key2'

If I want to query data by (c1 and c2) or (c3 and c4) or (c5 and c6), I need EXECUTE 3 times by

curl -v http://127.0.0.1:8093/query/service -d 'prepared="getResByKey1AndKey2"&$key1="c1"&$key2="c2"'
curl -v http://127.0.0.1:8093/query/service -d 'prepared="getResByKey1AndKey2"&$key1="c3"&$key2="c4"'
curl -v http://127.0.0.1:8093/query/service -d 'prepared="getResByKey1AndKey2"&$key1="c5"&$key2="c6"'

If I can only EXECUTE 1 time, It will be good.


#4

You need to write and PREPARE the correct query. Perhaps you need to use OR. Perhaps you need to use UNION ALL. You need to look into all these N1QL features. The bottom line is, the language provides all these capabilities.


#5

Hi @atom_yang, To examplify what @geraldss mentioned, with union all you could write a single statement that take key1…key6 and execute this in one batch. you add some more smartness like:

curl -v http://127.0.0.1:8093/query/service
-d ‘statement=PREPARE getResByKey1AndKey2 FROM
SELECT a.value from mydb a where a.key1 == $key1 and a.key2 == $key2 and $key1 is not null and $key1 is not missing and $key2 is not null and $key2 is not missing
UNION ALL
SELECT a.value from mydb a where a.key1 == $key1 and a.key2 == $key2 …
UNION ALL …’

May I ask what type of an app you are building and why this capability is important to you?
Thanks


#6

I am building a mobile app + SPA web application,
and I build it base on Couchbase Server 3.0 + Couchbase Mobile 1.1.0.

I want to migration to Couchbase Mobile 1.2.0 + Couchbase Server 4.1.0 to use the new feature,
In Couchbase Server 3.0, I used View/Reduce for SPA to query data,
and the keys,startkey/endkey feature is good for me,and my app is using it.

So I want to minimum change to migration my app to Couchbase Server 4.1.0.
What is more, I want to use N1QL on Couchbase-Lite.

I found N1QL is powerful,I just use N1QL to replace 22 view and 3 ElasticSearch queries using in my SPA app , It is so good to me.


#7

that is great to hear. Thanks - hope things are working well so far. let me know if there are other questions.
thanks
-cihan


#8

OK,I think it is a common requirement. any way, Thank you again.