Skip to main content
A
amittal-31-Visitor
1-Visitor
December 4, 2018
Solved

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

  • December 4, 2018
  • 1 reply
  • 2998 views

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

Best answer by amittal-3

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

1 reply

A
amittal-31-VisitorAuthor
1-Visitor
December 4, 2018

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*"
		}]
	}
};
R
rosharma16-Pearl
16-Pearl
December 4, 2018

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"
}
]
}
};

 

A
amittal-31-VisitorAuthorAnswer
1-Visitor
December 4, 2018

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

Terms & ConditionsAccessibility statement