Dynamic sort (order by), based on variable, in XQuery

Question

I'm trying to implement a dynamic sort in XQuery. I'm currently developing with Saxon-PE 9.5, but will be using the XQuery (or xqueries plural) in eXist and marklogic so any answers using their modules/functions is fine (and hopefully the other db will have a corresponding module/function).

The sort is based on a variable that contains a sequence of strings. Each string in the sequence is the name of an element and an optional "descending".

I've tried multiple ways but can't get anything to work the way it's supposed to; especially for secondary sorts.

In the following example, the sort is static and has a primary sort of c (ascending) and a secondary sort of b (descending)...

so_xquery_question.xml

<doc>
    <foo id="foo1">
        <a>a1</a>
        <b>b1</b>
        <c>c0</c>
    </foo>
    <foo id="foo2">
        <a>a2</a>
        <b>b2</b>
        <c>c0</c>
    </foo>
    <foo id="foo3">
        <a>a3</a>
        <b>b3</b>
        <c>c3</c>
    </foo>
</doc>

XQuery

let $xml := doc('file:///C:/SO/so_xquery_question.xml')

return
<test>{
for $foo in $xml/doc/foo
order by $foo/c, $foo/b descending
return
    $foo
}</test>

Output

<test>
   <foo id="foo2">
        <a>a2</a>
        <b>b2</b>
        <c>c0</c>
    </foo>
   <foo id="foo1">
        <a>a1</a>
        <b>b1</b>
        <c>c0</c>
    </foo>
   <foo id="foo3">
        <a>a3</a>
        <b>b3</b>
        <c>c3</c>
    </foo>
</test>

The output is sorted correctly; first by c (ascending) and then by b (descending).

My latest attempt partially works. (In Saxon and marklogic. It doesn't work the same in eXist for some unknown reason (!@#$).)

Here it is:

XQuery

let $orderby := ('c','b descending')
let $xml := doc('file:///C:/SO/so_xquery_question.xml')

return
<test>{
for $foo in $xml/doc/foo
order by
    if ($orderby='b') then $foo/b else (),
    if ($orderby='b descending') then $foo/b else () descending,
    if ($orderby='c') then $foo/c else (),
    if ($orderby='c descending') then $foo/c else () descending
    return
        $foo
}</test>

Output

<test>
   <foo id="foo3">
        <a>a3</a>
        <b>b3</b>
        <c>c3</c>
    </foo>
   <foo id="foo2">
        <a>a2</a>
        <b>b2</b>
        <c>c0</c>
    </foo>
   <foo id="foo1">
        <a>a1</a>
        <b>b1</b>
        <c>c0</c>
    </foo>
</test>

As you can see, it's first sorting on b (descending). This is because that is the order of the if statements in the order by; not on the order of the variable sequence ($orderby). If I swap the order of the ifs (test for c first), it sorts fine.

I also had this working in eXist, but it doesn't handle the descending:

order by util:eval(concat('$foo/',string-join(tokenize($orderby,'\s')[1],', $foo/')))

Is there any way I can do a dynamic sort that takes the following into account?

• Can pass the element names to sort on as a variable.
• Can specify optional "descending" for element names in the variable.
• Maintains the order of the variable (primary vs secondary sorting).

Answer 1

This is a hole in XQuery 1.0, and I don't think 3.0 has fixed it.

For the non-eval approach, have you tried something like this?

if ($orderby='b') then $foo/b
else if ($orderby='c') then $foo/c else (),
if ($orderby='b descending') then $foo/b
else if ($orderby='c descending') then $foo/c else () descending

However I would probably separate the key and direction into two different variables.

Answer 2

In eXist-db, it is possible to get a double sort by using util:eval(). I don't see why this should be necessary, but it works.

xquery version "3.0";
let $xml :=
<doc>
    <foo id="foo1">
        <a>a1</a>
        <b>b1</b>
        <c>c0</c>
    </foo>
    <foo id="foo2">
        <a>a2</a>
        <b>b2</b>
        <c>c0</c>
    </foo>
    <foo id="foo3">
        <a>a3</a>
        <b>b3</b>
        <c>c3</c>
    </foo>
</doc>
let $order-by := ('c','b descending')
let $sort :=
    if ($order-by[1] eq 'c' and $order-by[2] eq 'b descending')
    then 'for $foo in $xml/foo order by $foo/c, $foo/b descending return $foo'
    else ()
return
    util:eval($sort)

It's verbose - and of course the logic needs to be filled out (and you can concatenate $sort).

I see similar problems with secondary sorts based on a variable in BaseX and Zorba.