CODEWITHSUNDEEP
dynamicxquerymarklogic
StuBob
StuBob

Reputation: 1598

Ordering results dynamically in marklogic

So, Here is my question -
I want to order a list based off of a set of variables that determine the sorting fields and the order

In essence I need to do "order by" dynamically.

EG:

    declare function getSearchResults(
        $query as cts:query,
        $sort as xs:string*,
        $direction as xs:string*,    
) as element()*  {

        let $results := 
        cts:search(/*, $query)

        let $sortFields := fn:tokenize($sort, "\|")
        let $dec := $direction = 'desc' or $direction = 'descending'
        let $sorted := sortByFields($results, $sort,$dec)

        return $sorted
};


declare private function sortByFields ($results, $sortFields, $dec)
{
let $asc := fn:not($dec)
for $i in $results

order by

    if ($sortFields[1]='id' and $asc) then $i//ldse:document/@id else (),
    if ($sortFields[1]='id' and $dec) then $i//ldse:document/@id else () descending,

    if ($sortFields[1]='title' and $asc) then $i//title else (),
    if ($sortFields[1]='title' and $dec) then $i//title else () descending

return if (fn:count($sortFields) > 1 ) then (sortByFields($i,$sortFields[2 to fn:count($sortFields)],$dec)) else ($i)
};

This method will not work because it has to sort multiple times, and won't preserve the sort order each iteration.

I also tried this:

let $sortFields := fn:tokenize($sort, "\|")
let $dec := $direction = 'desc' or $direction = 'descending'
let $asc := fn:not($dec)
for $i in $results
    for $j in 1 to fn:count($sortFields)

    order by

        if ($sortFields[$j]='id' and $asc) then $i//ldse:document/@id else (),
        if ($sortFields[$j]='id' and $dec) then $i//ldse:document/@id else () descending,

        if ($sortFields[$j]='title' and $asc) then $i//title else (),
        if ($sortFields[$j]='title' and $dec) then $i//title else () descending

return $i

but this duplicates my data. (returns it ordered by each sort Field)

I would prefer not to use xdmp:eval because of code injection, is there any way that I can do this?

Any help or suggestions would be much appreciated.

Thanks a bunch!

Upvotes: 2

Views: 931

Answers (1)

DALDEI
DALDEI

Reputation: 3732

Several things to consider. If you are doing ordering on the results of a cts:search() then all the results must be returned before you can order them. That means you cannot do efficient pagination. E.g. if you have a million rows and want the top 100 ... if you order it dynamically then you have to fetch 1 million rows. If this is an issue then more complex solutions are needed.

Implementation wise, this is a good case for using function items ... but to make the ascending/decending work requires static analysis. Alternatively it can always be ascending (or decending) but the value be positive/negative. e.g.

for $r in cts:search(...) 
order by myfunc($r, $criteria)
return $r

declare function myfunc( $r , $criteria ) as xs:double
{
  ... logic to order $r in a natural ordering of -inf ... +f..
  return $ordering
};

but before digging into that, I would recommend looking at search:search() instead.

http://docs.marklogic.com/search:search#opt-sort-order

There is some very power features in this including being able to define complex ordering as an xml element.

Ultimately to do custom ordering efficiently you will probably need to create range indexes so that the ordering can be done in the server itself instead of your code. For small data sets it is not as big an issue, but when you start searching over thousands, hundreds of thousands or millions of documents you cant effectively pull them all into memory on every search (or even if you can it will be slow). Not only do the results all have to be pulled into to memory to start sorting, but the xquery code has to be evaluated for every term. Using indexes the result set can often be directly returned in the right without even loading the documents.

There are other techniques you can use such as loading the results into a map or an array, creating a self sorting tree structure, pre creating custom subsets of the data etc.

Take a look at the search:search library first ... you can even define your own search syntax for users to type in, all type and injection safe and very well optimized over years.

Upvotes: 5

Related Questions