Extension:VisualData/Queries

Queries

VisualData is able to query the data inserted through forms/schemas and to display them in various display formats using the parser functions visualdataquery and visualdataprint. The first is used to display data from multiple articles, and the second the data from a single article, also different from the article where the parser function is placed.

Here is an example of the use of the parser function visualdataquery for displaying name, latitude and longitude of all cities of the state Badakhshan in Afghanistan. (of course the related data have to be inserted in the wiki beforehand, see section Maintenance scripts for more information)

{{#visualdataquery:[[name::Afghanistan]] [[states/name::Badakhshan]]
|schema=Country 
|?states/cities/name
|?states/cities/latitude
|?states/cities/longitude
|format=datatable 
}}

The query uses a Extension:Semantic MediaWiki-like syntax and prints the results using datatables: see here for more information about result formats and templates.

Although VisualData' query engine might currently be less extensive than SMW query language it offers a few important additional features:

it allows to query hierarchical data, according to the intrinsic recursive nature of json-schema
it allows to have namespaced properties, since each set of properties depends by a specific schema and does not conflict with the names of other schemas

This way it is possible to store also a large amount of data in a single article, using a deeply nested schema (here for instance are stored all states and cities information of the country Afghanistan) and to query your data using the desired depth level (for instance latitude and longitude of each city, or the names of all states).

Since version 1.08b AND and OR logical operators are fully supported considering the square brackets as always concatenated by the AND operator and allowing each of them to contain an arbitrary number of OR operators whose subjects can be either printouts, categories or article titles.

Related schema

A query is always performed among the properties/values of a given schema, simply using the following parameter:

|schema=[my schema]

Conditions

The query is the first argument of the parser function visualdataquery and looks as follows:

[[name::Afghanistan]] [[states/name::Badakhshan]]

Each string surrounded by double square brackets represents a query condition, where the string before the double colon represents the property path (called "printout" in the dialect of Visual Data), and the string on the right is the value to be matched.

The following search operators are currently supported:

symbol	meaning
`>`	greater than
`>=`	greater or equal than
`<`	less than
`<=`	less or equal than
`!`	not
`~`	like
`+`	not null

This allows to safely perform standard SQL queries on your set of data. For instance the following condition

[[name::Afghanistan]] [[states/cities/name::B~]]

matches all json data (in our case with schema "contry") with property "name" with value "Afghanistan" and (within such schema) all cities with name starting with "B".

Specific pages or categories can be inserted in the query in the following way:

[[My Article]][[Category:Category a||Category:Category b]]

where the query will search for a page with title "My article" with category "Category a" or "Category b". In order to search a page with title "My article" with category "Category a" and "Category b" use:

[[My Article]][[Category:Category a]][[Category:Category b]]

A query can also contain just the article id or pagetitle (without being enclosed by square brackets) in order to retrieve data of a specific article as follows:

{{#visualdataquery:Data:Countries/Afghanistan
|schema=Country 
|?states/cities/name
|?states/cities/latitude
|?states/cities/longitude
|format=datatable 
}}

or

{{#visualdataquery:710
|schema=Country 
|?states/cities/name
|?states/cities/latitude
|?states/cities/longitude
|format=datatable 
}}

(this is especially useful in join queries —by which the result of a query is used as a condition for another query— or with the VisualData/API)

Logical operators

Conditions are concatenated by default using the AND operator. The OR operator is supported by using double pipes within the condition itself, as follows:

[[prop a::a||prop a:b]]

(this will match the property 'prop a' when its value is 'a' or 'b')

The same property can also be concatenated multiple times (using theAND operator) as follows:

[[prop a::!a]] [[prop a::!b]]

(this will match the property 'prop a' when its value is different from 'a' and 'b')

Printouts

In the specific dialect of VisualData (of course inherited from Semantic MediaWiki), a "printout" is a property path, therefore (technically) a json-pointer without array indexes and json-schema keywords. This way it is possible to easily reference nested property omitting technical details.

The printouts are indicated pre-pending a question mark to the parameter value:

|?states/cities/name
|?states/cities/latitude
|?states/cities/longitude

If no printouts are declared, the query will return all properties (including nested properties) of a given schema. (this however should be avoided since for large schemas the mysql query will exceed the number of supported joins)

Since version 1.0.8 it is also possible to specify an alternate name for the printouts especially used with the table or datatables result formats.

|?states/state_code=
|?states/name=State name
|?states/cities/name=City name
|?states/cities/latitude 
}}

The previous query will show the states column with name "State name", the cities column with header "City name", and while it will retrieve the "state_code" field for the use within templates, it won't be showed in the table.

Playing with printouts and printout templates

Both parser functions support the use of non existing printouts that will be rendered as empty columns for the table or datatables format. This is useful when used in conjunction with printout templates in order to render custom content within such columns, like action buttons based on VisualData's forms.

(Find out more here)

Format

Both the visualdataquery (and the visualdataprint) parser functions expect a format through which print the result. Find out more here.

Parameters

Here are the available parameters for the visualdataquery parser function and their default values.

parameter	description	default
`(unnamed parameter)`	one or more conditions
`schema`	the schema to be queried
`separator`	items separator
`values-separator`	values-seperator	`,`
`limit`	limit of the query as in SQL	`100`
`order`	equivalent to 'ORDER BY' in SQL language	e.g. `\|order=population desc`
`offset`	offset of the query as in SQL	`0`
`format`	result printer format	`json`
`template`	template name
`template?[printout]`	template applied to a specific printout, or subitem
`pagetitle`	name of the field containing the article holding the related data. If set to empty (using `pagetitle=`) the field will be omitted from the visualization	`page title`