CODEWITHSUNDEEP
restmarklogicmarklogic-9
Mayank Gupta
Mayank Gupta

Reputation: 31

I am installing a REST Extension in MarkLogic to query a view. How can I design this GET request so that it can take criteria like > and < for dates?

This question is more about REST URL design so that I can pass the criteria for the dates where I need to search for < or > a particular date. Also, this will be a REST Extension in MarkLogic which will invoke a SQL query in turn over a view. So the criteria passed in the query parameter will serve as a criterion for the SQL query.

Here is my document:

{
      "EmployeeID": "1234567",
      "EmployeeName": "Lorem Ipsum",
      "JoiningDate": "2018-07-01"
}

Below is the template definition:

var tde = require("/MarkLogic/tde.xqy");
var EmployeeRecordTDE = xdmp.toJSON(
  {
    "template": {
      "context": "/",
      "collections": ["EmployeeRecordCollection"],
      "rows": [
        {
          "schemaName": "employees",
          "viewName": "EmployeeRecordView",
          "columns": [
            {
              "name": "employeeId",
              "scalarType": "string",
              "val": "EmployeeID"
            },
            {
              "name": "employeeName",
              "scalarType": "string",
              "val": "EmployeeName",
            },
            {
              "name": "startDate",
              "scalarType": "string",
              "val": "JoiningDate"
            }
          ]
        }
      ]
    }
  }
);
tde.validate( 
  [EmployeeRecordTDE]
);

tde.nodeDataExtract( 
  [fn.collection(["EmployeeRecordCollection"])],
  [EmployeeRecordTDE]
);

tde.templateInsert(
  "/employee/EmployeeRecordTDE.json" ,
  EmployeeRecordTDE,
  xdmp.defaultPermissions(),
  ["EmployeeRecordCollection"]
)

I am thinking of querying above view somewhat like this:

op.fromView('employees', 'EmployeeRecordView',"")
     .select(['employeeId', 'employeeName', 'startDate'])
     .where(op.eq(op.col('employeeId'), op.param('emplId')))
     .orderBy('employeeId');

or may be like this:

op.fromSQL("select * from EmployeeRecordView where employees.EmployeeRecordView.employeeId=12345","")

I am flexible to use Optic API via Javascript or use SQL inside the REST Extension whichever works best. I just need the ability to be able to pass in query parameters for the various SQL criterias.

Let's say my REST extension is defined as 

http://localhost:8040/LATEST/config/resources/employees?method=get&get:emplId=integer&get:name=string

I can have the REST URL to cover '=' and 'in' criteria like this:

http://localhost:8040/LATEST/resources/employees?rs:name='John','Brad'&rs:emplId=1234567

Not sure how to pass parameters for between and date search scenarios that can work with MarkLogic.

Upvotes: 0

Views: 86

Answers (1)

ehennum
ehennum

Reputation: 7335

Optic provides the op.sqlCondition() function to filtering an Optic row set based on a simple SQL expression. See:

http://docs.marklogic.com/op.sqlCondition

Passing a simple expression is better than passing an entire SQL query from the client in that it reduces the potential for SQL injection attacks.

The REST resource service extension could take a single parameter that has the string to pass to op.sqlCondition().

The preferred approach would be to parameterize the string argument -- as in op.sqlCondition(op.param('filterExpr')) so the server can cache the query.

Hoping that helps,

Upvotes: 1

Related Questions