We're giving the support section some love, please forgive some broken links and ugly pages while we do so. If you can't find what you're after, please drop an email to support@kolola.net

Advanced Queries

You can use Impact Query Language (IQL) to create more precise queries and return precisely the activities that you are interested in.

How does it Work?

Using IQL is more powerful than the more basic search queries, but is also more complex and often slower. So, where possible, it's usually worth starting by trying out one of the more basic built-in queries available.

Syntax

IQL operates on the database tables within KOLOLA. The basic syntax is as follows:

"table_name"(filters);

Where table_name is the name of the table that should be searched for records, and filters is a list of filters to apply. Queries must end with a semicolon (;). In practice, queries need to be prefixed with iql: when used in the search box or analytics tool.

So for example, you could input the following queries in the search box:

iql:"event"("EventID"="1000"); // Returns the activity with ID 1000
igl:"person"("Name"*"d" & "Name"*"m"); // Returns people with names that contain a 'd' AND an 'm'

Tables

A list of common table names which you can use in queries is listed below:

  • event - activities themselves
  • person - people in the system
  • attachment - uploaded attachments
  • link - external web links
  • framework - assessment indicators
  • feature - assessment statements
  • evidence - types of evidence
  • eventevidence - evidence data
  • collection - event collections

Filters

There are two basic kinds of filter which are field filters and relation filters:

  • Field filters - filter records based on specific fields.
  • Relation filters - filter records based on their relation to other records in the database.

Field Filters

  1. "field_name" = "value": Find all the records where the named field is equal to the given value
  2. "field_name" < "value": Find all the records where the named field is less than the given value
  3. "field_name" > "value": Find all the records where the named field is greater than the given value
  4. "field_name" * "value": Find all the records where the named field contains (as text) the given value, anywhere inside it

Note - table names, field names and values all need to be enclosed with double quotes, "name"

Relation Filters

  • [another_query]: Find all records that are related to a record that's returned by the given query. For example, to find all activities that are related to an attachment
    event([attachment]);

Combining Filters

You can combine multiple filters using the boolean operators & (for and) and | (for or).

Example Queries

"event"(["person"("Username"="administrator")]); // Returns events related to person with username administrator
"person"("Name"*"d"); // Returns people with names that contain a 'd'
"person"("Name"*"d" | "Name"*"m"); // Returns people with names that contain a 'd' OR an 'm' (or both)
"event"("TypeID"="12"); // Find all events where the TypeID is 12.  Type IDs can be found in the "Event Types"
section of the framework editor

Data Dictionary

The lists below contain the fields which form each table. These fields can be used to create queries for very specific data.

attachment

Uploaded attachments

ColumnTypeNullDefault Comments
PhotoID (Primary)int(11)No
PersonIDint(11)No
EventIDint(11)No
Filenamevarchar(255)No
Permissionenum('Private', 'Users', 'Public')No
DescriptiontextNo

collection

Basic information about user-created activity collections

ColumnTypeNullDefault Comments
CollectionID (Primary)int(11)No
Titlevarchar(100)No
DescriptiontextNo
Colourvarchar(7)No

collectionevent

Associate activities with the collections that they've been added to

ColumnTypeNullDefault Comments
CollectionEventID (Primary)int(11)No
CollectionIDint(11)No
EventIDint(11)No

collectionowner

Associates a collection with the people who are allowed to modify it

ColumnTypeNullDefault Comments
CollectionOwnerID (Primary)int(11)No
CollectionIDint(11)No
PersonIDint(11)No

event

Basic information about activities

ColumnTypeNullDefault Comments
EventID (Primary)int(11)No
Namevarchar(150)No
StartDatedateYesNULL
EndDatedateYesNULL
DescriptiontextYesNULL
Locationvarchar(45)YesNULL
Organisationvarchar(45)YesNULL
TypeIDint(11)No
CalEventIDvarchar(50)YesNULL

eventevidence

Associates a piece of evidence with an activity and the person who provided it

ColumnTypeNullDefault Comments
EventEvidenceID (Primary)int(11)No
EventIDint(11)No
EvidenceIDint(11)No
PersonIDint(11)No
Valuevarchar(255)No

eventfeature

Associates activities with assessment statements

ColumnTypeNullDefault Comments
EventFeatureID (Primary)int(11)No
EventIDint(11)No
FeatureIDint(11)No

eventparticipant

Associates people with activities

ColumnTypeNullDefault Comments
EventParticipantID (Primary)int(11)No
EventIDint(11)No
PersonIDint(11)No
CommenttextYesNULL

evidence

Defines types of evidence that can be requested for activities

ColumnTypeNullDefault Comments
EvidenceID (Primary)int(11)No
Typeenum('text', 'integer', 'attachment', 'link')No
Namevarchar(80)No
DescriptiontextNo
Globaltinyint(1)No
OptionstextYesNULL

feature

Contains framework assessment statements and associates them with an indicator

ColumnTypeNullDefault Comments
FeatureID (Primary)int(11)No
FrameworkIDint(5)No
Statementvarchar(255)No

framework

Contains framework indicators

ColumnTypeNullDefault Comments
FrameworkID (Primary)int(11)No
Themevarchar(45)YesNULL
Indicatorvarchar(100)YesNULL
DescriptortextYesNULL

link

ColumnTypeNullDefault Comments
LinkID (Primary)int(11)No
PersonIDint(11)No
EventIDint(11)No
URLvarchar(255)No

person

ColumnTypeNullDefault Comments
PersonID (Primary)int(11)No
Usernamevarchar(45)No
Emailvarchar(255)YesNULL
Passwordvarchar(40)No
Statusenum('Active', 'Disabled')No
ResetTokenvarchar(40)No
Namevarchar(45)No
Avatarvarchar(45)YesNULL
CompetitionGroupIDvarchar(20)No
IsAdmintinyint(1)No
Typeenum('User', 'Guest')No
Orcidvarchar(45)YesNULL

What can I use this for?

Advanced queries can be used to access virtually any data stored in the database. This can be useful when you need a specific bit of information or want to explore the relationship between different people and activities.