xmlsortingxquerymarklogicexist-db

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

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?

Solution

  • While trying to implement @mblakele's suggestion, I did get this to work...

    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[1]='b') then $foo/b else (),
    if ($orderby[1]='b descending') then $foo/b else () descending,
    if ($orderby[1]='c') then $foo/c else (),
    if ($orderby[1]='c descending') then $foo/c else () descending,
    if ($orderby[2]='b') then $foo/b else (),
    if ($orderby[2]='b descending') then $foo/b else () descending,
    if ($orderby[2]='c') then $foo/c else (),
    if ($orderby[2]='c descending') then $foo/c else () 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>

    What I'm doing is checking the first item in the sequence for possible values, then checking the second item in the sequence. This will ensure the order of the sequence is maintained.

    Pros:

    Cons: