Creating a composite query (with AND and OR type) for Datatable

[amittal-3]

Hello,

I would like to create a composite query (single query) which has types of both AND and OR. For better explanation consider that datatable (say DT_A) has four columns (say 'name' [type: STRING], 'description' [type: STRING], 'remarks' [type: STRING] and 'date' [type: DATETIME]).

Now I would like to create a single query which can give me results for - 

"GET datatable entries when 'name' or 'description' or 'remarks' is LIKE '*asd*' and 'date' equals (EQ) '1543881600'"

I referred couple of links as mentioned below, but I am not able to create a single query for above statement

https://support.ptc.com/cs/help/thingworx_hc/thingworx_7.0_hc/index.jspx?id=QueryParameterforQueryServices&action=show

https://community.ptc.com/t5/ThingWorx-Developers/How-to-query-form-infotable-using-query-function/td-p/505431

Please let me know in case some more information is required to address this problem.

Thanks in advance.

Regards

Aditya Mittal

[amittal-3]

I would also like to mention that as per my QUERY statement, I have created the following query, but in this query I am not able to manage my 'date' parameter

var query =
{
  "filters": {
	"type": "OR",
	"filters": [
		{
			"type": "LIKE",
			"fieldName": "name",
			"value": "*asd*"
		},
		{
			"type": "LIKE",
			"fieldName": "description",
			"value": "*asd*"
		},
		{
			"type": "LIKE",
			"fieldName": "remarks",
			"value": "*asd*"
		}]
	}
};

[rosharma]

ideally if you use "create query for infotable" function that will ask for parameters ( i.e DataShapes and the filter parameters) -- in filter , select for fields which are required in your query.

similar type of query will be generated for type 'AND' as you pasted above

var query = {
"filters": {
"type": "AND",
"filters": [
{
"fieldName": "name",
"type": "LIKE",
"value": "ro*"
},
{
"fieldName": "id",
"type": "EQ",
"value": "5"
}
]
}
};

[amittal-3 · Accepted solution]

Hello @rosharma,

Thanks for your reply. Yes we can create query using the way you suggested, but as I mentioned I wanted the mix on AND and OR type query, and using that technique we can either get AND or OR type of query.

Anyways just now I was able to figure out the way to create that query. Just for reference I am posting that query.

var query =
{
  "filters": {
    "type": "AND",
	"filters": [{
		"type": "OR",
		"filters": [
			{
				"type": "LIKE",
				"fieldName": "name",
				"value": "*asd*"
			},
			{
				"type": "LIKE",
				"fieldName": "description",
				"value": "*asd*"
			},
			{
				"type": "LIKE",
				"fieldName": "remarks",
				"value": "*asd*"
			}]
	    },
		{
			"type": "EQ",
			"fieldName": "Date",
			"value": "1543881600"
		}]
	}
};

Regards

Aditya

[slangley]

Hi @amittal-3.

For the benefit of others on the community, please mark the appropriate reply as the Accepted Solution to this post.

Regards.

--Sharon