Entities and values to browse and/or select are represented in the query by
variables that must be written in capital letters.

With RQL, we do not distinguish between entities and attributes. The value of an
attribute is considered as an entity of a particular type (see below), linked to
one (real) entity by a relation called the name of the attribute, where the
entity is the subject and the attribute the object.

The possible type(s) for each variable is derived from the schema according to
the constraints expressed above and thanks to the relations between each
variable.

We can restrict the possible types for a variable using the special relation
is in the restrictions.

Those relations may only be used in RQL query but are not actual attributes of
your entities.

has_text: relation to use to query the full text index (only for entities
having fulltextindexed attributes).

identity: relation to use to tell that a RQL variable is the same as another
when you’ve to use two different variables for querying purpose. On the
opposite it’s also useful together with the NOT operator to tell that two
variables should not identify the same entity

The LIKE string operator can be used with the special character % in
a string as wild-card:

-- match every entity whose name starts with 'Th'AnyXWHEREXname~='Th%'-- match every entity whose name endswith 'lt'AnyXWHEREXnameLIKE'%lt'-- match every entity whose name contains a 'l' and a 't'AnyXWHEREXnameLIKE'%l%t%'

ILIKE is the case insensitive version of LIKE. It’s not
available on all backend (e.g. sqlite doesn’t support it). If not available for
your backend, ILIKE will behave like LIKE.

~= is a shortcut version of ILIKE, or of LIKE when the
former is not available on the back-end.

The REGEXP is an alternative to LIKE that supports POSIX
regular expressions:

-- match entities whose title starts with a digitAnyXWHEREXtitleREGEXP"^[0-9].*"

The GROUPBY keyword is followed by a list of terms on which results
should be grouped. They are usually used with aggregate functions, responsible to
aggregate values for each group (see Aggregate functions).

For grouped queries, all selected variables must be either aggregated (i.e. used
by an aggregate function) or grouped (i.e. listed in the GROUPBY
clause).

The ORDERBY keyword if followed by the definition of the selection
order: variable or column number followed by sorting method (ASC,
DESC), ASC being the default. If the sorting method is not
specified, then the sorting is ascendant (ASC).

The LIMIT and OFFSET keywords may be respectively used to
limit the number of results and to tell from which result line to start (for
instance, use LIMIT 20 to get the first 20 results, then LIMIT 20 OFFSET 20
to get the next 20.

AnyXWHERENOTXowned_byU means “entities that have no relation
owned_by”.

AnyXWHERENOTXowned_byU,Ulogin"syt" means “the entity have no
relation owned_by with the user syt”. They may have a relation “owned_by”
with another user.

In this clause, you can also use EXISTS when you want to know if some
expression is true and do not need the complete set of elements that make it
true. Testing for existence is much faster than fetching the complete set of
results, especially when you think about using OR against several expressions. For instance
if you want to retrieve versions which are in state “ready” or tagged by
“priority”, you should write :

the former will retrieve all versions, then check for each one which are in the
matching state of or tagged by the expected tag,

the later will retrieve all versions, state and tags (cartesian product!),
compute join and then exclude each row which are in the matching state or
tagged by the expected tag. This implies that you won’t get any result if the
in_state or tag tables are empty (ie there is no such relation in the
application). This is usually NOT what you want.

Another common case where you may want to use EXISTS is when you
find yourself using DISTINCT at the beginning of your query to
remove duplicate results. The typical case is when you have a
multivalued relation such as Version version_of Project and you want
to retrieve projects which have a version:

AnyPWHEREVversion_ofP

will return each project number of versions times. So you may be
tempted to use:

DISTINCTAnyPWHEREVversion_ofP

This will work, but is not efficient, as it will use the SELECTDISTINCT SQL predicate, which needs to retrieve all projects, then
sort them and discard duplicates, which can have a very high cost for
large result sets. So the best way to write this is:

AnyPWHEREEXISTS(Vversion_ofP)

You can also use the question mark (?) to mark optional relations. This allows
you to select entities related or not to another. It is a similar concept
to Left outer join:

the result of a left outer join (or simply left join) for table A and B
always contains all records of the “left” table (A), even if the
join-condition does not find any matching record in the “right” table (B).

You must use the ? behind a variable to specify that the relation to
that variable is optional. For instance:

Bugs of a project attached or not to a version

AnyX,VWHEREXconcernsP,Peid42,Xcorrected_inV?

You will get a result set containing all the project’s tickets, with either the
version in which it’s fixed or None for tickets not related to a version.

All cards and the project they document if any

AnyC,PWHERECisCard,P?documented_byC

Notice you may also use outer join:

on the RHS of attribute relation, e.g.

AnyXWHEREXrefXR,YnameXR?

so that Y is outer joined on X by ref/name attributes comparison

on any side of an HAVING expression, e.g.

AnyXWHEREXcreation_dateXC,Ycreation_dateYCHAVINGYEAR(XC)=YEAR(YC)?

so that Y is outer joined on X by comparison of the year extracted from their
creation date.

The HAVING clause, as in SQL, may be used to restrict a query
according to value returned by an aggregate function, e.g.

AnyXGROUPBYXWHEREXrelationYHAVINGCOUNT(Y)>10

It may however be used for something else: In the WHERE clause, we are
limited to triplet expressions, so some things may not be expressed there. Let’s
take an example : if you want to get people whose upper-cased first name equals to
another person upper-cased first name. There is no proper way to express this
using triplet, so you should use something like:

Notice that while we would like this to work without the HAVING clause, this
can’t be currently be done because it introduces an ambiguity in RQL’s grammar
that can’t be handled by Yapps, the parser’s generator we’re using.

The WITH keyword introduce sub-queries clause. Each sub-query has the
form:

V1(,V2) BEING (rql query)

Variables at the left of the BEING keyword defines into which
variables results from the sub-query will be mapped to into the outer query.
Sub-queries are separated from each other using a comma.

Let’s say we want to retrieve for each project its number of versions and its
number of tickets. Due to the nature of relational algebra behind the scene, this
can’t be achieved using a single query. You have to write something along the
line of:

Notice that we can’t reuse a same variable name as alias for two different
sub-queries, hence the usage of ‘X’ and ‘XX’ in this example, which are then
unified using the special identity relation (see Virtual relations).

Warning

Sub-queries define a new variable scope, so even if a variable has the same name
in the outer query and in the sub-query, they technically aren’t the same
variable. So:

Also, when a variable is coming from a sub-query, you currently can’t reference
its attribute or inlined relations in the outer query, you’ve to fetch them in
the sub-query. For instance, let’s say we want to sort by project name in our
first example, we would have to write:

extract from the string a string starting at given index and of
given length

LIMIT_SIZE(String,maxsize)

if the length of the string is greater than given max size,
strip it and add ellipsis (“…”). The resulting string will
hence have max size + 3 characters

TEXT_LIMIT_SIZE(String,format,maxsize)

similar to the above, but allow to specify the MIME type of the
text contained by the string. Supported formats are text/html,
text/xhtml and text/xml. All others will be considered as plain
text. For non plain text format, sgml tags will be first removed
before limiting the string.