Last modified on 17 November 2011, at 23:13

XQuery/Limiting Result Sets

MotivationEdit

Sometimes you have many records or instances in a collection and you want to limit the amount of data returned by a query.

StrategyEdit

Limiting Records in a DocumentEdit

If you are limiting records in a large XML document you can do this by adding a predicate to the end of your for loop:

for $person in doc($file)/Person[position() lt 10]

Using Subsequence to Limit ResultsEdit

The following query retrieves only the first 10 documents, in sequential document order, contained within a collection.

  for $person in subsequence(collection($my-collection)/person, 1, 10)

Where:

  subsequence($sequence, $starting-item, $number-of-items)

Note that the first argument is the item to start at and the second is the total number of items to return. It is NOT the last item to return.

Sorting Before Limiting ResultsEdit

Note that usually you will want to get the first N documents based on some sorting criteria, for example people that have a last name that start with the letter "A". So the first thing you must do is create a list of nodes that have the correct order and then get the first N records from that list. This can usually be done by creating a temporary sequence of sorted items as a separate FLWOR expression.

let $sorted-people :=
   for $person in collection($collection)/person
   order by $person/last-name/text()
   return $person
 
for $person at $count in subsequence($sorted-people, $start, $records)
return
   <li>$person/last-name/text()</li>

Adding Buttons to Get Next N RecordsEdit

Getting the Next N RowsEdit

After you fetch the first N items from your sequence you frequently want to get the next N rows. To do this you will need to add buttons to your report for "Previous N Records" and "Next N Records". Thse buttons will pass parameters to your XQuery telling where to start and how many records to fetch.

To do this you will need to call your own script with different parameters on the URL. To keep track of the URL you can use the get-url() function that comes with eXist. For example: let $query-base := request:get-url() If your query was run from http:/localhost:8080/exist/rest/db/apps/user-manager/views/list-people.xq this is the string that would be returned.

We will also get two parameters from the URL for the record to start at and the number of records to fetch:

  let $start := xs:integer(request:get-parameter("start", "1"))
  let $num := xs:integer(request:get-parameter("num", "20"))

Now we will create two HTML buttons that allow us to get next N records or the previous N records.

  <input type="button" onClick="parent.location='{$query-base}?start={$start - $num}&num={$num}'" value="< Previous"/>
  <input type="button" onClick="parent.location='{$query-base}?start={$start + $num}&num={$num}'" value="Next >"/>

Full Example ProgramEdit

xquery version "1.0";
declare namespace xmldb="http://exist-db.org/xquery/xmldb";
declare namespace u="http://niem.gov/niem/universal/1.0";
declare option exist:serialize "indent=yes";
 
let $start := xs:integer(request:get-parameter("start", "1"))
let $num := xs:integer(request:get-parameter("num", "5"))
let $query-base := request:get-url()
 
return
<html>
   <title>Contacts</title> 
   <body>
      <h1>Contacts</h1>
      <table border="1">
         <thead>
            <tr>
               <th>ID</th>
               <th>Last Name</th>
               <th>First</th>
               <th>Street</th>
               <th>City</th>
               <th>State</th>
               <th>Zip</th>
               <th>EMail</th>
               <th>Phone</th>
               <th colspan="2">Function</th>
            </tr>
          </thead>
      <tbody>
      {
      for $person in subsequence(collection('/db/contacts/data'), $start, $num)/Person
        let $pid := $person/id
        let $lname := $person/u:PersonSurName/text()
        let $fname := $person/u:PersonGivenName/text()
        let $street := $person/u:StreetFullText/text()
        let $city := $person/u:LocationCityName/text()
        let $state := $person/u:LocationStateName/text()
        let $zip := $person/u:LocationPostalCodeID/text()
        let $email := $person/u:ContactEmailID/text()
        let $phone := $person/u:TelephoneNumberFullID/text()
        order by $lname, $fname
        return
        <tr>
           <td>{$pid}</td>
           <td>{$lname}</td>
           <td>{$fname}</td>
           <td>{$street}</td>
           <td>{$city}</td>
           <td>{$state}</td>
           <td>{$zip}</td>
           <td>{$email}</td>
           <td>{$phone}</td>
           <td><a href="update-person-form.xq?id={$pid}">Edit</a></td>
           <td><a href="delete-person.xq?id={$pid}">Delete</a></td>
        </tr>
      }
      </tbody>
    </table>
    <input type="button"
       onClick="parent.location='{$query-base}?start={$start - $records}&amp;num={$num}'" value="&lt; Previous"/>
    <input type="button"
       onClick="parent.location='{$query-base}?start={$start + $records}&amp;num={$num}'" value="Next &gt;"/>
    <br/>
    <a href="create-person.xhtml">Create New Person</a>
    <br/>
    <a href="index.xhtml">Return to main demo page</a>
  </body>
</html>