Syntax error - at $4 in N1QL Template

SQL++
1

My problem is that i allow users to search for many different things and the way do it currently is to go and create templates for each query. As i return this data for a grid i have to go and have 2 queries to get the total count found which meets the search criteria and then the actual query to get the data with the limit and offset.

So in my current example i have a query which takes optional filters so i need to create a template for each posible scenario like user searches by field A only, or B only or C only and then if Possibly A and B only or A and C only so the list of templates gets larger.

Here is an example for the where part

FROM Contacts AS f
LEFT JOIN Contacts AS t
ON t._type ="tract_info" AND ANY b IN t.tract_id SATISFIES b = f.tract END
WHERE f._type="farm" and f.tract IN $3 
order by f.PropertyAddress.streetName, TONUMBER(f.PropertyAddress.houseNumber)
Limit $1 Offset $2

So now thought to save my self some extra templates i modify it like this

FROM Contacts AS f
 LEFT JOIN Contacts AS t
 ON t._type ="tract_info" AND ANY b IN t.tract_id SATISFIES b = f.tract END
 WHERE f._type="farm" and f.tract IN $3 $4
 order by f.PropertyAddress.streetName, TONUMBER(f.PropertyAddress.houseNumber)
 Limit $1 Offset $2

and send $4 as “and ARRAY_COUNT(f.phones) > 0 and ARRAY_COUNT(f.emails) > 0”

which creates me the folowing error

{ Error: syntax error - at $4
    at C:\nodeRoot\CRM-NodeJS\node_modules\couchbase\lib\bucket.js:918:17
  requestID: '1bb85162-bdf5-41c1-91bc-d53ddabca245',
  code: 3000,
  otherErrors: [],
  responseBody:
   '{\n"requestID": "1bb85162-bdf5-41c1-91bc-d53ddabca245",\n"errors": [{"code":3000,"msg":"syntax error - at $4"}],\n"status": "fatal",\n"metrics": {"elapsedTime": "36.773401ms","executionTime": "36.45979ms","resultCount": 0,"resultSize": 0,"errorCount": 1}\n}\n' }

But if i create the template like this it works just fine. So what am i missing here or how can i build more flexible templates

    FROM Contacts AS f
     LEFT JOIN Contacts AS t
     ON t._type ="tract_info" AND ANY b IN t.tract_id SATISFIES b = f.tract END
     WHERE f._type="farm" and f.tract IN $3 and ARRAY_COUNT(f.phones) > 0 and ARRAY_COUNT(f.emails) > 0
     order by f.PropertyAddress.streetName, TONUMBER(f.PropertyAddress.houseNumber)
     Limit $1 Offset $2
2

Query parameters are meant for values not the query text.
You can from the query template to query string in application or SDK and issue N1QL statement from the string.

3

i am a bit confused about the app or SDK. Do you mean just build the string dynamicly outside couchbase and then pass it to query ?
Currently i have a huge libary of ftemplates which i decide to call based on what user is filtering on, then i pass the template and array of values to my function

let statement = this.N1qlQuery.fromString(n1qlStr)
statement.consistency(this.N1qlQuery.Consistency.REQUEST_PLUS)
bucket.query(statement, id, (err, result, meta)=>{
4

Yes. you can construct n1qlStr can be dynamically construct.
In your case the following is syntactically not correct (because $4 is extra that is why it returns error)

WHERE f._type=“farm” and f.tract IN $3 $4

query parameters are execution values. The statement must able to parse syntax, semantically and able generate plan.