How to fetch the records in same order as in Encoded Query

[THOUSIF S N1]

I am querying a knowledge table. Now i want to fetch the record details in the same order as mentioned in the enodedQuery.?

can anyone help me.

var gr = new GlideRecord('kb_knowledge');
gr.addEncodedQuery("numberINKB0097860,KB0085324,KB0104996,KB0049906,KB0213906,KB0366019,KB0084936,KB0141043,KB0433398");
gr.query();
while(gr.next()){
gs.info(gr.number)
}  

[Ankur Bawiskar]

@THOUSIF S N1 

you can order it in ascending or descending using orderBy() or orderByDesc()

var gr = new GlideRecord('kb_knowledge');
gr.orderBy('number'); // use this for ascending
gr.orderByDesc('number'); // use this for descending
gr.addEncodedQuery("numberINKB0097860,KB0085324,KB0104996,KB0049906,KB0213906,KB0366019,KB0084936,KB0141043,KB0433398");
gr.query();
while(gr.next()){
gs.info(gr.number)
}

[-O-]

That is not possible, it is not something that RDBMSes, or SQL do.

What you could do is create an array containing the numbers in the order you want, load the records as properties of an object, where the property keys are the numbers, than map the original array replacing array items with the cache object's properties with the same number.

E.g. running

var numbersToFetch = ['KB0010007', 'KB0000962', 'KB0040002'];

gs.debug('\n\nFetch in order: ' + numbersToFetch.join(', '));
gs.debug(JSON.stringify(getRecordsInSpecificOrder(numbersToFetch), null, '\t'));

function getRecordsInSpecificOrder (numbersToFetch) {
	var cache = {};
	var gr = new GlideRecord('kb_knowledge');

	gr.addQuery('number', numbersToFetch);

	gr._query();

	gs.debug('\n\nRetrieved in order:');

	while (gr._next()) {
		cache['' + gr.number] = {
			'author': gr.author.getDisplayValue(),
			'number': '' + gr.number,
			'valid_to': gr.valid_to.getDisplayValue(),
		};

		gs.debug('' + gr.number);
	}

	gs.debug('\n\nResult in fetched order:');
	gs.debug(JSON.stringify(cache, null, '\t'))
	gs.debug('\n\nResult in requested order:');

	return numbersToFetch.map(replaceKeyWithPropertyOf(cache));
}

function replaceKeyWithPropertyOf (cache) {
	return function replace (key) {
		return cache[key];
	};
}

Prints out:

*** Script: [DEBUG] 

Fetch in order: KB0010007, KB0000962, KB0040002
*** Script: [DEBUG] 

Retrieved in order:
*** Script: [DEBUG] KB0000962
*** Script: [DEBUG] KB0040002
*** Script: [DEBUG] KB0010007
*** Script: [DEBUG] 

Result in fetched order:
*** Script: [DEBUG] {
	"KB0000962": {
		"author": "System Administrator",
		"number": "KB0000962",
		"valid_to": "15.06.2100"
	},
	"KB0040002": {
		"author": "System Administrator",
		"number": "KB0040002",
		"valid_to": "01.01.2100"
	},
	"KB0010007": {
		"author": "System Administrator",
		"number": "KB0010007",
		"valid_to": ""
	}
}
*** Script: [DEBUG] 

Result in requested order:
*** Script: [DEBUG] [
	{
		"author": "System Administrator",
		"number": "KB0010007",
		"valid_to": ""
	},
	{
		"author": "System Administrator",
		"number": "KB0000962",
		"valid_to": "15.06.2100"
	},
	{
		"author": "System Administrator",
		"number": "KB0040002",
		"valid_to": "01.01.2100"
	}
]

As you can see the order in which records have been fetched is one (KB0000962, KB0040002 and KB0010007) - different from the query order (KB0010007, KB0000962, KB0040002). But in the end the loaded data is returned in the original requested order.

[THOUSIF S N1]

Thank you for the reply.

I just did it.

Solution: I stored all the article numbers in an array and then used for loop to iterate one by one it worked Thank you

[-O-]

While that is also a solution, it is a very inefficient one.