Marklogic Optic lib op:sql-condition not working with multiple LIKE Conditions

Question

I am trying to filter the documents based on this condition using Xquery optic lib in marklogic. But op:sql-condition doesn't seem to work as expected.

op:sql-condition("Employee.employeeName LIKE '%JOHN%' AND Employee.employeeName LIKE '%Smith%' AND Employee.employeeName LIKE '%Eric%'")

Using this condition I am hoping to get all the documents which has those employee names, but it only returns the first one from the condition. In this condition, it is JOHN.

How do I update this query to work with AND having multiple conditions?

Answer 1

According to the developer documentation:

... the filter definition cannot be used in an Optic Boolean expression but, instead, must be the only argument to the WHERE call.

and further:

The condition must be a valid simple SQL Boolean expression expressed as a string.

So this means: no boolean operations inside the SQL expression but just a boolean result.

You can join multiple where conditions to achieve an "and" operation between them. I expanded the example from the documentation page a bit to show that:

xquery version "1.0-ml";

import module namespace op="http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

op:from-literals((
  map:entry("id", 1) => map:with("name", "Master 1") => map:with("date", "2015-12-01"),
  map:entry("id", 2) => map:with("name", "Master 2") => map:with("date", "2015-12-02"),
  map:entry("id", 3) => map:with("name", "Master 3") => map:with("date", "2015-12-03"),
  map:entry("id", 4) => map:with("name", "Master 4") => map:with("date", "2015-12-04")))

=> op:where(op:sql-condition("id BETWEEN 0.5 AND 3.5"))
=> op:where(op:sql-condition("id > 2"))
=> op:result()

This gives the entry with the ID 3 as a result, because it's ID is between 0.5 and 3.5 AND is greater than 2. You should be able to apply it to three individual like statements as well.