diff -r 058bb3dc685f -r 0b59724cb3f2 cubicweb/web/wdoc/tut_rql_en.rst --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/cubicweb/web/wdoc/tut_rql_en.rst Sat Jan 16 13:48:51 2016 +0100 @@ -0,0 +1,258 @@ +.. -*- coding: utf-8 -*- + +Let's learn RQL by practice... + +.. contents:: + +Introduction +------------ + +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_. + +.. _here: ../schema + + +Some bits of theory +------------------- + +Variables et types +~~~~~~~~~~~~~~~~~~ +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". + +Base types +~~~~~~~~~~ +* `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` + +Opérateurs +~~~~~~~~~~ +* 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') + +Grammaire des requêtes de recherche +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +:: + + [DISTINCT] V1(, V2)* + [GROUPBY V1(, V2)*] [ORDERBY ] + [WHERE ] + [LIMIT ] [OFFSET ] + +:entity type: + Type of the selected variable(s). You'll usually use `Any` type to not specify + any type. +:restriction: + List of relations to follow, in the form + `V1 relation V2|` +:orderterms: + 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. + + +Example schema +-------------- + +In this document, we will suppose the application's schema is the one described +here. Available entity types are : + +:Person: + :: + + name (String, required) + birthday (Date) + + +:Company: + :: + + name (String) + + +:Note: + :: + + diem (Date) + type (String) + + +And relations between those entities: :: + + Person works_for Company + Person evaluated_by Note + Company evaluated_by Note + + +Meta-data +~~~~~~~~~ +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: + +:CWUser: + :: + + login (String) not null + password (Password) + firstname (String) + surname (String) + + +Basis queries +------------- +0. *Every persons* :: + + Person X + + or :: + + Any X WHERE X is Person + + +1. *The company named Logilab* :: + + Company S WHERE S name 'Logilab' + + +2. *Every entities with a "name" attribute whose value starts with 'Log'* :: + + Any S WHERE S name LIKE 'Log%' + + or :: + + Any S WHERE S name ~= 'Log%' + + This query may return Person or Company entities. + + +3. *Every persons working for the Logilab company* :: + + Person P WHERE P works_for S, S name "Logilab" + + or :: + + Person P WHERE P works_for S AND S name "Logilab" + + +4. *Company named Caesium ou Logilab* :: + + Company S WHERE S name IN ('Logilab','Caesium') + + or :: + + Company S WHERE S name 'Logilab' OR S name 'Caesium' + + +5. *Every company but ones named Caesium ou Logilab* :: + + Company S WHERE NOT S name IN ('Logilab','Caesium') + + or :: + + Company S WHERE NOT S name 'Logilab' AND NOT S name 'Caesium' + + +6. *Entities evaluated by the note with eid 43* :: + + Any X WHERE X evaluated_by N, N eid 43 + + +7. *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. + + +8. *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). + + + +Advanced +-------- +0. *Person with no name specified (i.e NULL)* :: + + Person P WHERE P name NULL + + +1. *Person which are not working for any company* :: + + Person P WHERE NOT p works_for S + + +2. *Every company where person named toto isn't working* :: + + Company S WHERE NOT P works_for S , P name 'toto' + + +3. *Every entity which have been modified between today and yesterday* :: + + Any X WHERE X modification_date <= TODAY, X modification_date >= TODAY - 1 + + +4. *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 + + +5. *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