Couchbase Query N1QL with Place Holders

SQL++
1

I wa wondering what the exact limitations for placeholders are for N1QL querys. I am trying to pass a ORDER BY via $3 in but that does not seem to work.

My N1QL Template looks like this

SELECT META().id AS DocId,
d.first_name || " " || IFMISSINGORNULL(d.middle_name || " ", “”) || d.last_name AS Name,
UPPER(SUBSTR(d.first_name, 0,1) || SUBSTR(d.last_name, 0,1) )AS initial,
d.addresses,
d.phones AS phones,
d.emails AS emails,
(
SELECT text,
META().id AS DocId
FROM Contacts USE KEYS d.buckets) AS buckets,
(
SELECT text,
META().id AS DocId
FROM Contacts USE KEYS d.tags) AS tags
FROM Contacts AS d
WHERE _type = ‘contact’
ORDER BY $3
Limit $1 Offset $2

when i execute it seems it is not using the ORDER BY option if i use it in my Console it sorts just fine.

2

bucket names, field names, identifiers can’t be place holders. place holders constant values that change from query to query

ORDER BY normally expression on field names, unless you want order by constant which is no meaning.

In console you might be executing query with place holders (this may not be using prepare statements), i.e. every time it prepares statement i.e adhoc=true

3

So basically i cant pass a fieldname as $3 for the order by clause. Whats interesting is that it just ignores the order by in that case or uses a blank order by and not throw an error.

4

place holders are values. values are constant. i.e every document has same value, which sorts on duplicate it can get any order unless additional info provided how to sort on duplicates.

You must $3 one of the projected value of the subquery

SELECT d1.*
FROM (SELECT META().id AS DocId,
           d.first_name || " " || IFMISSINGORNULL(d.middle_name || " ", "") || d.last_name AS Name,
           UPPER(SUBSTR(d.first_name, 0,1) || SUBSTR(d.last_name, 0,1) ) AS initial,
           d.addresses,
           d.phones AS phones,
           d.emails AS emails,
           ( SELECT text, META().id AS DocId FROM Contacts USE KEYS d.buckets) AS buckets,
           ( SELECT text, META().id AS DocId FROM Contacts USE KEYS d.tags) AS tags
      FROM Contacts AS d
      WHERE _type = "contact") AS d1
ORDER BY d1.[$3]
LIMIT $1
OFFSET $2;

Also check out this https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

5

Thanks but that becomes to complicated to manage, i rather build the query in nodeJS then