"Relation Query Language" tutorial
Let's learn RQL by practice...
RQL has a syntax and principle inspirated from the SQL query language, though it's at a higher level in order to be more intuitive and suitable to easily make advanced queries on a schema based database.
- the main idea in RQL is that we'are following relations between entities
- attributes are a special case of relations
- RQL has been inspirated from SQL but is at a higher level
- a knowledge of the application'schema is necessary to build rql queries
To use RQL, you'll have to know the basis of the language as well as a good knowledge of the application'schema. You can always view it using the "schema" link in user's dropdown menu (on the top-right of the screen) or by clicking here.
Entities and attributes'value to follow and / or select are represented by the query by variables which must be written upper-case.
Possible types for each variable are deducted from the schema according to constraints in the query.
You can explicitly constrain a variable's type using the special relation "is".
- String (literal: between double or simple quotes)
- Int, Float (using '.' as separator)
- Date, Datetime, Time (literal: string YYYY/MM/DD[ hh:mm] or TODAY and NOW keywords)
- Boolean (keywords TRUE et FALSE)
- keyword NULL
Logical operators : AND, OR, ,
Mathematical operators: +, -, *, /
Comparisons operators: =, <, <=, >=, >, ~=, LIKE, IN
= is the default comparison operator
LIKE / ~= permits use of the special character % in a string to tell the string must begin or end with a prefix or suffix (as SQL LIKE operator)
Any X WHERE X name ~= 'Th%' Any X WHERE X name LIKE '%lt'
IN permits to give a list of possible values
Any X WHERE X name IN ('chauvat', 'fayolle', 'di mascio', 'thenault')
[DISTINCT] <entity type> V1(, V2)* [GROUPBY V1(, V2)*] [ORDERBY <orderterms>] [WHERE <restriction>] [LIMIT <value>] [OFFSET <value>]
Type of the selected variable(s). You'll usually use Any type to not specify any type.
Define a selection order : variable or column number, followed by the sort method (ASC, DESC), with ASC as default when not specified
notice about grouped query (e.g using a GROUPBY clause): every selected variable should be either grouped or used in an aggregat function.
In this document, we will suppose the application's schema is the one described here. Available entity types are :
name (String, required) birthday (Date)
diem (Date) type (String)
And relations between those entities:
Person works_for Company Person evaluated_by Note Company evaluated_by Note
Every entities'type have the following meta-data:
- eid (Int), a unique identifier
- creation_date (Datetime), date on which the entity has been created
- modification_date (Datetime), lastest date on which the entity has been modified
- created_by (CWUser), relation to the user which has created this entity
- owned_by (CWUser), relation to the user()s considered as owner of this entity, the entity's creator by default
- is (Eetype), special relation to specify a variable type.
A user's entity has the following schema:
login (String) not null password (Password) firstname (String) surname (String)
Any X WHERE X is Person
The company named Logilab
Company S WHERE S name 'Logilab'
Every entities with a "name" attribute whose value starts with 'Log'
Any S WHERE S name LIKE 'Log%'
Any S WHERE S name ~= 'Log%'
This query may return Person or Company entities.
Every persons working for the Logilab company
Person P WHERE P works_for S, S name "Logilab"
Person P WHERE P works_for S AND S name "Logilab"
Company named Caesium ou Logilab
Company S WHERE S name IN ('Logilab','Caesium')
Company S WHERE S name 'Logilab' OR S name 'Caesium'
Every company but ones named Caesium ou Logilab
Company S WHERE NOT S name IN ('Logilab','Caesium')
Company S WHERE NOT S name 'Logilab' AND NOT S name 'Caesium'
Entities evaluated by the note with eid 43
Any X WHERE X evaluated_by N, N eid 43
Every persons order by birthday from the youngest to the oldest
Person X ORDERBY D DESC WHERE X birthday D
Notice you've to define a variable using the birthday relation to use it in the sort term.
Number of persons working for each known company
Any S, COUNT(X) GROUPBY S WHERE X works_for S
Notice you've that since you're writing a grouped query on S, X have to be either grouped as well or used in an aggregat function (as in this example).
Person with no name specified (i.e NULL)
Person P WHERE P name NULL
Person which are not working for any company
Person P WHERE NOT p works_for S
Every company where person named toto isn't working
Company S WHERE NOT P works_for S , P name 'toto'
Every entity which have been modified between today and yesterday
Any X WHERE X modification_date <= TODAY, X modification_date >= TODAY - 1
Every note without type, to be done in the next 7 days, ordered by date
Any N, D where N is Note, N type NULL, N diem D, N diem >= TODAY, N diem < today + 7 ORDERBY D
Person with an homonym (without duplicate)
DISTINCT Person X,Y where X name NX, Y name NX
or even better (e.g. without both (Xeid, Yeid) and (Yeid, Xeid) in the results)
Person X,Y where X name NX, Y name NX, X eid XE, Y eid > XE