Nested DSL conditions in N1QL where clause

Hi all,

I am trying to express the following N1QL query via the SDK:

N1ql query:

select * from `books` where name = "harry potter" and (author = "rowling" or publisher = "penguin");

So far I have gotten:
Same query using DSL:

AsPath prefix = select("*").from(i("books"));
Statement query1 = prefix.
        where(
        		x("name").eq(s("harry potter")).
        		and
        		(
        				x("author").eq(s("rowling"))
        				.or
        				(x("publisher").eq(s("penguin")))
        		)
        	);
N1qlQuery query = N1qlQuery.simple(statement);

But when I do, the above code snippet yields a different N1QL query statement:

System.out.println(query.statement().toString());

Output

select * from `books` where name = "harry potter" and author = "rowling" or publisher = "penguin";

Basically, the expected parentheses are missing.

How can I change the above code snippet to retain the parentheses?

Regards,

Hi @akshar.punuganti, you can always use the par() static method to wrap in parantheses. See this commit for details: https://github.com/couchbase/couchbase-java-client/commit/e30ab99d5ad17ba4e1045710fc5a395476aeac9e

Cheers,
Michael

Does not work when I add parentesis

SELECT *, meta().cas FROM HRN2jDQDZl WHERE ( publisherTenantId.value = “publisherTenantId_1” OR ANY p IN publisherIds SATISFIES p.value = “publisherTenantId_1”) AND doc = “ScreenGroup” END

[
  {
    "code": 3000,
    "msg": "syntax error - at )",
    "query_from_user": "SELECT *, meta().cas FROM `HRN2jDQDZl`  WHERE ( publisherTenantId.`value` = \"publisherTenantId_1\"  OR ANY p IN publisherIds SATISFIES p.`value` = \"publisherTenantId_1\")  AND doc = \"ScreenGroup\" END"
  }
]

Did you tired back quotes around value due to reserve keyword

SELECT *, meta().cas 
FROM HRN2jDQDZl
 WHERE ( publisherTenantId.`value` = "publisherTenantId_1" OR 
               ANY p IN publisherIds SATISFIES p.`value`= "publisherTenantId_1") 
              AND doc = "ScreenGroup" END

Also doc is not referenced by array loop variable. If that is outside array you may want to move the condition outside ANY reduce cpu cycles.

yes back quotes are used around value

I’m new to Couchbase, what do you mean by “move the condition outside ANY reduce cpu cycles.”

I think I made it work, I move END in ANY (I belive this is what you meant):

SELECT *, meta().cas
FROM lV1k6vmwVk
WHERE ( publisherTenantId.value = “publisherTenantId_1” OR
ANY p IN publisherIds SATISFIES p.value= "publisherTenantId_1"END)
AND doc = “ScreenGroup”

SELECT *, meta().cas 
FROM  lV1k6vmwVk
 WHERE ( publisherTenantId.`value` = "publisherTenantId_1" OR 
               ANY p IN publisherIds SATISFIES p.`value`= "publisherTenantId_1" END 
              AND doc = "ScreenGroup");

ANY p IN publisherIds SATISFIES p.value= "publisherTenantId_1" END
Is Looping construct stops when condition is true or looping completed. If you add the condition that not depends on looping inside it keep evaluate each iteration. By moving outside it evaluates once.

1 Like