5

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).
Daniel Haley
  • 51,389
  • 6
  • 69
  • 95

5 Answers5

2

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.

mblakele
  • 7,782
  • 27
  • 45
  • I had thought about it, but hadn't tried it. My actual data has 8 possible elements that might be specified in the `$orderby` variable and the number of `if` statements will be nasty. I'll give it a shot though and see what happens. Hopefully eXist can handle it as well as marklogic does. +1 for a good suggestion – Daniel Haley May 20 '14 at 20:42
  • I don't think I can get this to work; especially if I have more than 2 possible elements to sort on. Even just sorting on 2, I'm not sure how I could modify your suggestion to also work on a variable with the value `('c descending','b')`. The primary sort will always either be `b` or `c` depending on the order of the `if` statements. – Daniel Haley May 20 '14 at 21:12
  • I did come up with one way to do it, but it's still super verbose. I'll add it as an answer, but hopefully someone has a better solution (that works in both eXist and marklogic). – Daniel Haley May 20 '14 at 21:13
1

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:

  • It works.

Cons:

  • It's super verbose and will be ugly for my 8 possible element names (128 different if statements!!).
  • It still doesn't work in eXist.
Daniel Haley
  • 51,389
  • 6
  • 69
  • 95
  • I had a similar sort problem, and without eval, this was the best I could do. The eXist problem could be a bug, though. – wst May 21 '14 at 15:19
1

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.

Jens Østergaard Petersen
  • 412
  • 2
  • 6
  • Thanks for the suggestion. I'm not sure this is feasible for my actual data. There are just too many possibilities for me to account for and still have code that is maintainable. I might go this route though if I decide to limit the sort options. Thanks again! – Daniel Haley May 21 '14 at 17:05
1

In eXist 6 (the current stable release), both scenarios in the original post work as expected:

xquery version "3.1";

let $xml := 
    document { 
        <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>
    }
return
    <test>{
        for $foo in $xml/doc/foo
        order by $foo/c, $foo/b descending
        return
            $foo
    }</test>

returns:

<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>

Scenario 2

The only modification is that instead of returning a () this returns an empty string "" - pending a fix to the issue I filed yesterday: https://github.com/eXist-db/exist/issues/4252.

xquery version "3.1";

let $xml := 
    document { 
        <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 $orderby := ('c','b descending')
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>

Returns:

<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>
Joe Wicentowski
  • 5,159
  • 16
  • 26
0

I'd use eval() for the whole sort :

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 $sort-fields := ("c descending","a")
let $xquery := concat(
                  "for $row in $xml/* order by ", 
                   string-join(
                         for $field in $sort-fields 
                         return concat("$row/",$field) ,", ") ,
                  " return $row"
               )
return
element test {
    util:eval($xquery)
    }
Chris Wallace
  • 495
  • 3
  • 11