Last modified on 3 October 2011, at 18:46

XQuery/Searching,Paging and Sorting

These examples use a simple XML file containing data on Earthquakes around the world. The data come from Swivel. The examples use the generic table-viewer introduced in "Creating Custom Views" for output.

SearchingEdit

This example searches for a string in the location of the earthquake.

declare option exist:serialize  "method=xhtml media-type=text/html indent=yes";
import module namespace wikiutil = "http://www.cems.uwe.ac.uk/xmlwiki" at  "util.xqm";

let $search := request:get-parameter("search","")
let $matches := //Earthquake[contains(Location,$search)]
return
<html>
    <head>
         <title>Search Earthquakes for {$search}</title>
     </head>
     <body>
     <h1>Search Earthquakes</h1>
     <form>Search for <input type="text" name="search" value="{$search}"/>
     </form>
     {
       wikiutil:sequence-to-table($matches)
     }
     </body>
   </html>

Execute

PagingEdit

This script implements paging of the search results. Here the full search is repeated for each call, with the state of the interaction held in a hidden input.

declare option exist:serialize  "method=xhtml media-type=text/html indent=yes";
import module namespace  wikiutil = "http://www.cems.uwe.ac.uk/xmlwiki" at  "util.xqm";

let $search := request:get-parameter("search","")
let $start:= xs:integer(request:get-parameter("start", "1"))
let $records := xs:integer(request:get-parameter("records", "5"))
let $action := request:get-parameter("action","search")

let $allMatches := //Earthquake[contains(Location,$search)]

(: compute the limits for this page :)
let $max := count($result)
let $start := 
      if ($action = "Previous") 
      then max(($start - $records, 1))
      else if ($action="Next")
      then  if ($max <$start +$records) 
            then $start 
            else $start +$records
      else if ($action="Search")
      then 1
      else $start
let $end :=  min (($start + $records - 1,$max))
 
(: restrict the full set of matches to this subsequence :)
let $matches := subsequence($allMatches,$start,$records)

return
<html>
      <head>
         <title>Search Earthquakes </title>
     </head>
     <body>
        <h1>Search Earthquakes</h1>
        <form >
              Search Location for <input type="text" name="search" value="{$search}"/> 
              <input type="submit" name="action" value="Search"/>
              <br/>
              <input type="hidden" name="start" value="{$start}"/>
              <input type="submit" name="action" value="Previous"/> 
              <input type="submit" name="action" value="Next"/>
              <p>Displaying {$start} to {$end} out of {$max} records found.</p>
              {wikiutil:sequence-to-table($matches) }
              <p>Records per Page <input type="text" name="records" value="{$records}"/></p>
        </form>
     </body>
</html>

Execute

SortingEdit

To get the columns sorted, we add a submit button to each column. This requires extending the generic table viewer to sort the nodes by the selected column.

declare function wikiutil:sequence-to-table($seq,$sort) {
  <table border="1">
     <tr>
         {for $node in $seq[1]/*
         return <th><input type="submit" name="Sort" value="{name($node)}"/></th> 
        }   
        </tr>
      {for $row in $seq
        let $sortBy := data($row/*[name(.) = $sort])
        order by $sortBy
        return
         <tr>
            {for $node in $seq[1]/*
             let $data := data($row/*[name(.)=name($node)])
             return <td>{$data}</td> 
        } 
         </tr>
      }
   </table>
 };
declare option exist:serialize  "method=xhtml media-type=text/html indent=yes";
import module namespace  wikiutil = "http://www.cems.uwe.ac.uk/xmlwiki" at  "util.xqm";

let $search := request:get-parameter("search","")
let $sort := request:get-parameter("Sort","Date")
let $matches := //Earthquake[contains(Location,$search)]
return
<html>
    <head>
       <title>Search Earthquakes}</title>
     </head>
     <body>
       <h1>Search Earthquakes</h1>
       <form>Search Location for <input type="text" name="search" value="{$search}"/>
         {wikiutil:sequence-to-table($matches,$sort)}
       </form>
     </body>
</html>

Note that the sort is by string value: Sorting by magnitude succeeds only by chance, whereas the sort on Fatalities does not.

Execute

An improvement would be to allow successive clicks to a column heading to reverse the sort direction. This requires the addition of two more items into the interaction state, the current sort order and current direction, and changes to the table generator. One would like to be able to say something like:

   for $row ..
   let $sortBy := ..
   let $direction := if (..) then "ascending" else "descending"
   order by $sortBy $direction

but this is not a valid FLWOR expression. Instead we have to have two FLWOR expressions, one for each direction.

 declare function wikiutil:sequence-to-table($seq,$sort,$direction) {
 <table border="1">
     <tr>
         {for $node in $seq[1]/*
         return <th><input type="submit" name="Sort" value="{name($node)}"/></th> 
        }   
      </tr>
      { if ($direction = 1) 
       then 
        for $row in $seq
        let $sortBy := data($row/*[name(.) = $sort])
        order by $sortBy ascending
        return
         <tr>
            {for $node in $seq[1]/*
             let $data := data($row/*[name(.)=name($node)])
             return <td>{$data}</td> 
           } 
         </tr>
      else 
         for $row in $seq
        let $sortBy := data($row/*[name(.) = $sort])
        order by $sortBy descending
        return
         <tr>
            {for $node in $seq[1]/*
             let $data := data($row/*[name(.)=name($node)])
             return <td>{$data}</td> 
           } 
         </tr>    
      }
   </table>
 };

Then the script becomes:

import module namespace  wikiutil = "http://www.cems.uwe.ac.uk/xmlwiki" at  "util.xqm";

declare option exist:serialize  "method=xhtml media-type=text/html indent=yes";

let $search := request:get-parameter("search","")
let $sort := request:get-parameter("Sort","Date")
let $lastSort := request:get-parameter("LastSort","")
let $lastDirection := number(request:get-parameter("LastDirection","1"))
let $direction := if ($lastSort = $sort) then - $lastDirection else 1
let $matches := //Earthquake[contains(Location,$search)]
return
<html>
    <head>
         <title>Search Earthquakes</title>
     </head>
     <body>
     <h1>Search Earthquakes</h1>
     <form>Search Location for <input type="text" name="search" value="{$search}"/>
     <input type="hidden" name="LastSort" value="{$sort}"/>
     <input type="hidden" name="LastDirection" value="{$direction}"/>
     {  wikiutil:sequence-to-table($matches,$sort, $direction)   }
      </form>
     </body>
</html>

Execute

Using a table schemaEdit

Greater control over the output can be obtained by providing a schema for the table. This schema can specify the order of columns and column headings, and potentially conversion instructions as well.

We can provide the table schema as a sequence of Column definitions:

  <Schema>
      <Column name="Location" heading="Earthquake location"/>
      <Column name="Magnitude" heading="Magnitude (Richter Scale)"/>
     <Column name="Date" />
   </Schema>

The schema-based function looks like:

declare function wikiutil:sequence-to-table-with-schema($seq,$schema) {
  <table border="1">
     <tr>
        {for $column in $schema/Column
         return <th>{string( ($column/@heading,$column/@name)[1])}</th> 
        }
     </tr>
      {for $row in $seq
       return
         <tr>
            {for $column in $schema/Column
             let $data := data($row/*[name(.)=$column/@name])
             return <td>{$data}</td> 
           } 
         </tr>
      }
   </table>
 };


Note the use of a XQuery idiom to compute the column heading as the supplied heading if there is one, otherwise the node name:

  ($column/@heading,$column/@name)[1]

This computes the first non-null item in the sequence, a cleaner and more generalisable alternative to:

  if (exists($column/@heading))
  then $column/@heading
  else $column/@name


Execute