WikiDB/Querying data

From TestWiki
Jump to: navigation, search

WikiDB is a tool to create and query database-like data from within your wiki.

This page describes how to query the data in your wiki.

Quick queries via the viewdata page[edit]

The easiest way to run a query on a single table is to visit the 'View Data' tab (for example, here is the viewdata page for Table:Companies).

This page provides a paged view of the full set of data defined for the table, and provides the ability to filter the list by specifying a criteria or to change the order by specifying one or more columns to sort by.

Embedded queries via the <repeat> tag[edit]

The second method of querying the results is to use a <repeat> tag inside a wiki page, which allows you to output a dynamically-updated query as part of the page content. As well as the ability to filter and sort the data, you are also able to control how the output looks in a number of ways.

Here is a basic example, to give an overview of the syntax:

 <repeat table="..." criteria="..." sort="...">Any old wiki text, with {{{variable}}} substitution.</repeat>

For full details, see WikiDB/Repeat tag syntax.

Specifying criteria[edit]

Criteria strings currently support a basic set of comparison operators, boolean operators and parentheses, which should be sufficient for a large number of situations. They do not currently support arithmetic operators or functions.

The full list of supported operators can be found in the index.

You may use identifiers (field names, e.g. [[FirstName]]) or literal strings (e.g. "Brian") as operands. If these are unquoted then items on the left-hand-side of the operator are assumed to be identifiers, and items on the right-hand-side are assumed to be literals. However, if they are quoted then they may be placed on either side:

 FirstName = Brian            -- Matches all records containing the string "Brian" in the FirstName field.
 Brian = FirstName            -- Matches all records containing the string "FirstName" in the Brian field
                              -- (probably not what was intended).
 "Brian" = [[FirstName]]      -- Items are quoted, so order doesn't matter - equivalent to first example.
 'Brian' = `FirstName`        -- Single quotes and backtick quoting are also supported, for literals/identifiers, respectively.

Specifying sort order[edit]

When specifying the sort order of the result, you simply list the fields that you want to sort on in the order of precedence, e.g. FirstName, LastName. If you want to reverse the sort for one of the columns, add DESC after the column name, e.g. Age DESC, FirstName, LastName.