Conditional DQL sort

[marina_pollehn]

I often struggle with the current if statement options in DQL. 

I have created a timeseries with a metric which I called memory. The memory variable is based on either the maximum, minimum or average memory, depending on which value is used in the filter.

So far so good....

If I am looking at the minimum memory, I want to sort with | sort memort asc, if the memory is the maximum or average, I want to sort like |sort memory desc.

The initial idea was to add a variable (SortOrder) based on the following query:

fetch logs
| fields order=if($Aggregation=="Min","asc",else:"desc")
| limit 1

This variable will always be set to the string "desc" or "asc" automatically. Nevertheless, as it is a string, I cannot reference to it in my DQL tile:

| sort memory $SortOrder

is not allowed as a string is not allowed. Is there any way to convert the variable into a useful format? I am aware of the convert functions but I do not know what I would want to convert it to... Or another way to do conditional sorting?

[MostafaHussein]

I've asked CoPilot to do this and as the following screenshot

it worked when removed this part of my question "based on variable named `ordering`" so it worked without setting ordering variable as the following screenshot

I'm considering this a good challenge that will help solving many tricks in the future if succeeded so I'm now digging deeper within it and will inform you once i get the desired result. mostly it can be doable when using DQL within javascript code as we can make 2 separate DQL queries , 1st one will get what's the ordering and the 2nd one will use it in separate execution.

BR,
Mostafa Hussein.

[sinisa_zubic]

hi @marina_pollehn 

I see here 2 ways of solving this. If you really want to rely on one variable, then you have to execute two queries combined with an append. Always one of the queries will use a filter condition which is false for every record.

//desc sorting
fetch logs, from: $dt_timeframe_from, to:$dt_timeframe_to
| filter timestamp >= if($aggregation == "max", astimestamp($dt_timeframe_from), else: now()+1d)
| sort timestamp desc

//asc sorting
| append [
  fetch logs, from: $dt_timeframe_from, to:$dt_timeframe_to
| filter timestamp >= if($aggregation == "min", astimestamp($dt_timeframe_from), else: now()+1d)
| sort timestamp asc
]

Or you introduce a new Variable called direction with values "descending" and "ascending". Then you could use it like following:

fetch logs
| sort timestamp, direction: $direction

Best,
Sini

[krzysztof_hoja]

You can do it with just one query and single variable. Because you want to sort by field "memory" which is numeric, we can take advantage following fact: sorting in ascending order is equivalent to sorting in descending order but by negative value of our fields. We can simulate this behavior in this example query:

fetch logs
| fields memory = stringLength(content)
| fieldsAdd sort_order= if($aggregation=="min", -memory, else:memory)
| sort sort_order desc

If you select "min", sort will be descending but by -memory, so it will be ascending ascending order by memory. If you select not "min" (so "max") it will be just plain descending order.