How to add a "sorts" object to an existing query object

[VladimirRosu]

Hi dear community.

Note: I do have a workaround for the situation below, and that is to Sort separately the result of the QDTE service, but I was wondering why this method of attaching the sorts object does not work in the way I designed it.

Problem:
I have a relatively simple scenario where I encountered an issue. In ThingWorx 8.5.4, I have an advanced grid, bound to a custom service that has a query object as a QUERY-type parameter.

EnableGridSearch is enabled, and the widget's QueryFilter is bound to the service's query input parameter.

In my custom service I want to sort the results of a QueryDataTableEntries based on a "timestamp" field (DATETIME) to make sure the grid always displays the most recent field as the first item in the grid.

Because of the fact that the grid will always supply a "filters" section in my query object, I would like to attach my "sorts" key to what comes back from the grid.

I can attach the "sorts" key to the object (by setting it straight as an array - query.sorts = [{"fieldName": "timestamp","isAscending": false}]; Declaring it as [] or new Array() does not work)

However, the issue is that the sorts does not have an effect on the QDTE service if it's attached to the initial query object.

For reference, the final constructed object that gets passed to QDTE is the following:

{
    "filters": {
        "filters": [
            {
                "fieldName": "ID",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "sourceType",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "location",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "timestamp",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "Source",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "source",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "User",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "key",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "ServiceName",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "Content",
                "type": "LIKE",
                "value": "%pull%"
            },
            {
                "fieldName": "tags",
                "type": "LIKE",
                "value": "%pull%"
            }
        ],
        "type": "OR"
    },
    "sorts": [
        {
            "isAscending": false,
            "fieldName": "timestamp"
        }
    ]
}

I have the feeling that I'm maybe not constructing the array required by the sorts in a proper way?

[nmilleson]

@VladimirRosu ,

I gave this a shot and I ran into the same issue as well.  After doing some logging, it looks like if you try and set query.sorts directly, it creates a strange object:

{"filters":{"filters":{},"type":"OR"},"sorts":{"0":{"fieldName":"unit_price"}}}

Note the "0": key that should not be in the sorts object.  As a workaround, I attempted to try:

query.sorts = [];

query.sorts.push({fieldName: "unit_price"});

However, I was met with an error:

TypeError: Cannot find function push in object org.mozilla.javascript.NativeArray@6018346a.

The NativeArray object does not have a 'push' method.  However, it does have an 'add' method.  Unfortunately when I tried the add() method, it threw yet another error:

Wrapped java.lang.UnsupportedOperationException

Maybe it's possible to somehow parse a query object to JSON, modify, and then parse back to a query object

[nmilleson]

Quick update..

I did try this and it seems to work!

var queryTemp = JSON.stringify(query);
var query = JSON.parse(queryTemp);

query.sorts = [{fieldName: "myField"}];

[VladimirRosu]

Ah, ok, that was the reverse of a thing I tried. Will try to do later and will comment back.

Thanks!

[VladimirRosu · Accepted solution]

@nmilleson thanks for that update. That allowed me to make some crucial progress forward, and I was able to solve this issue for good.

So, things to note for a future viewer (note that the service input parameter is QUERY and it's bound directly to the Grid's Query):

-if the query object is undefined (not passed at all), you need to instantiate it somehow, 

query=new Object();

works fine;

That's only required when your QueryDataTableEntries is executed on Mashup loaded - the query object would be undefined.

-if the query object is set the first time as an empty object by the Grid's Filter mechanism, on the mashup initial load, the result object is not a JSON object (even if typeof returns object, but typeof does not discriminate well typically). In this case, doing a

query = JSON.parse(query);

fixes that issue.

My final code is this:

if (!query) {
	query = new Object();
} else {
	query = JSON.parse(query);
}
query.sorts = [{
	fieldName: "timestamp",
	isAscending: false
}];

result = me.QueryDataTableEntries({
	maxItems: 100 /* NUMBER */ ,
	query: query /* QUERY */
});