cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Level 9

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

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=QueryParameterforQuerySe...

 

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

 

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

 

Thanks in advance.

Regards

Aditya Mittal

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions

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

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

4 REPLIES 4

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

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

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

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

 

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

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

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

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