Mercurial Mercurial > pub > hg > cubicweb / comparison
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | comparison | raw | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
doc/book/en/annexes/rql/language.rst
branchstable
changeset 9801 e0532c37c9c2
parent 9330 45e0034e2a73
child 10378 ad0cea3ab218
equal deleted inserted replaced
9771:2656ac7fa9a8 9801:e0532c37c9c2 
   348 
   348 
   349 * the former will retrieve all versions, then check for each one which are in the
 
   349 * the former will retrieve all versions, then check for each one which are in the
 
   350   matching state of or tagged by the expected tag,
 
   350   matching state of or tagged by the expected tag,
 
   351 
   351 
   352 * the later will retrieve all versions, state and tags (cartesian product!),
 
   352 * the later will retrieve all versions, state and tags (cartesian product!),
 
   353   compute join and then exclude each row which are in the matching state of or
 
   353   compute join and then exclude each row which are in the matching state or
 
   354   tagged by the expected tag. This implies that : you won't get any result if the
 
   354   tagged by the expected tag. This implies that you won't get any result if the
 
   355   in_state or tag
 
   355   in_state or tag tables are empty (ie there is no such relation in the
 
       
   356   application). This is usually NOT what you want.
 
   356 
   357 
   357 Another common case where you may want to use ``EXISTS`` is when you
 
   358 Another common case where you may want to use ``EXISTS`` is when you
 
   358 find yourself using ``DISTINCT`` at the beginning of your query to
 
   359 find yourself using ``DISTINCT`` at the beginning of your query to
 
   359 remove duplicate results. The typical case is when you have a
 
   360 remove duplicate results. The typical case is when you have a
 
   360 multivalued relation such as Version version_of Project and you want
 
   361 multivalued relation such as Version version_of Project and you want
 
   560 
   561 
   561 .. _RQLAggregateFunctions:
 
   562 .. _RQLAggregateFunctions:
 
   562 
   563 
   563 Aggregate functions
 
   564 Aggregate functions
 
   564 ```````````````````
 
   565 ```````````````````
 
   565 +--------------------+----------------------------------------------------------+
 
   566 +------------------------+----------------------------------------------------------+
 
   566 | :func:`COUNT`      | return the number of rows                                |
 
   567 | ``COUNT(Any)``         | return the number of rows                                |
 
   567 +--------------------+----------------------------------------------------------+
 
   568 +------------------------+----------------------------------------------------------+
 
   568 | :func:`MIN`        | return the minimum value                                 |
 
   569 | ``MIN(Any)``           | return the minimum value                                 |
 
   569 +--------------------+----------------------------------------------------------+
 
   570 +------------------------+----------------------------------------------------------+
 
   570 | :func:`MAX`        | return the maximum value                                 |
 
   571 | ``MAX(Any)``           | return the maximum value                                 |
 
   571 +--------------------+----------------------------------------------------------+
 
   572 +------------------------+----------------------------------------------------------+
 
   572 | :func:`AVG`        | return the average value                                 |
 
   573 | ``AVG(Any)``           | return the average value                                 |
 
   573 +--------------------+----------------------------------------------------------+
 
   574 +------------------------+----------------------------------------------------------+
 
   574 | :func:`SUM`        | return the sum of values                                 |
 
   575 | ``SUM(Any)``           | return the sum of values                                 |
 
   575 +--------------------+----------------------------------------------------------+
 
   576 +------------------------+----------------------------------------------------------+
 
   576 | :func:`COMMA_JOIN` | return each value separated by a comma (for string only) |
 
   577 | ``COMMA_JOIN(String)`` | return each value separated by a comma (for string only) |
 
   577 +--------------------+----------------------------------------------------------+
 
   578 +------------------------+----------------------------------------------------------+
 
   578 
   579 
   579 All aggregate functions above take a single argument. Take care some aggregate
 
   580 All aggregate functions above take a single argument. Take care some aggregate
 
   580 functions (e.g. ``MAX``, ``MIN``) may return `None` if there is no
 
   581 functions (e.g. ``MAX``, ``MIN``) may return `None` if there is no
 
   581 result row.
 
   582 result row.
 
   582 
   583 
   583 .. _RQLStringFunctions:
 
   584 .. _RQLStringFunctions:
 
   584 
   585 
   585 String transformation functions
 
   586 String transformation functions
 
   586 ```````````````````````````````
 
   587 ```````````````````````````````
 
   587 
   588 
   588 +---------------------------------------------------+-----------------------------------------------------------------+
 
   589 +-----------------------------------------------+-----------------------------------------------------------------+
 
   589 | :func:`UPPER(String)`                             | upper case the string                                           |
 
   590 | ``UPPER(String)``                             | upper case the string                                           |
 
   590 +---------------------------------------------------+-----------------------------------------------------------------+
 
   591 +-----------------------------------------------+-----------------------------------------------------------------+
 
   591 | :func:`LOWER(String)`                             | lower case the string                                           |
 
   592 | ``LOWER(String)``                             | lower case the string                                           |
 
   592 +---------------------------------------------------+-----------------------------------------------------------------+
 
   593 +-----------------------------------------------+-----------------------------------------------------------------+
 
   593 | :func:`LENGTH(String)`                            | return the length of the string                                 |
 
   594 | ``LENGTH(String)``                            | return the length of the string                                 |
 
   594 +---------------------------------------------------+-----------------------------------------------------------------+
 
   595 +-----------------------------------------------+-----------------------------------------------------------------+
 
   595 | :func:`SUBSTRING(String, start, length)`          | extract from the string a string starting at given index and of |
 
   596 | ``SUBSTRING(String, start, length)``          | extract from the string a string starting at given index and of |
 
   596 |                                                   | given length                                                    |
 
   597 |                                               | given length                                                    |
 
   597 +---------------------------------------------------+-----------------------------------------------------------------+
 
   598 +-----------------------------------------------+-----------------------------------------------------------------+
 
   598 | :func:`LIMIT_SIZE(String, max size)`              | if the length of the string is greater than given max size,     |
 
   599 | ``LIMIT_SIZE(String, max size)``              | if the length of the string is greater than given max size,     |
 
   599 |                                                   | strip it and add ellipsis ("..."). The resulting string will    |
 
   600 |                                               | strip it and add ellipsis ("..."). The resulting string will    |
 
   600 |                                                   | hence have max size + 3 characters                              |
 
   601 |                                               | hence have max size + 3 characters                              |
 
   601 +---------------------------------------------------+-----------------------------------------------------------------+
 
   602 +-----------------------------------------------+-----------------------------------------------------------------+
 
   602 | :func:`TEXT_LIMIT_SIZE(String, format, max size)` | similar to the above, but allow to specify the MIME type of the |
 
   603 | ``TEXT_LIMIT_SIZE(String, format, max size)`` | similar to the above, but allow to specify the MIME type of the |
 
   603 |                                                   | text contained by the string. Supported formats are text/html,  |
 
   604 |                                               | text contained by the string. Supported formats are text/html,  |
 
   604 |                                                   | text/xhtml and text/xml. All others will be considered as plain |
 
   605 |                                               | text/xhtml and text/xml. All others will be considered as plain |
 
   605 |                                                   | text. For non plain text format, sgml tags will be first removed|
 
   606 |                                               | text. For non plain text format, sgml tags will be first removed|
 
   606 |                                                   | before limiting the string.                                     |
 
   607 |                                               | before limiting the string.                                     |
 
   607 +---------------------------------------------------+-----------------------------------------------------------------+
 
   608 +-----------------------------------------------+-----------------------------------------------------------------+
 
   608 
   609 
   609 .. _RQLDateFunctions:
 
   610 .. _RQLDateFunctions:
 
   610 
   611 
   611 Date extraction functions
 
   612 Date extraction functions
 
   612 `````````````````````````
 
   613 `````````````````````````
 
   613 
   614 
   614 +--------------------------+----------------------------------------+
 
   615 +----------------------+----------------------------------------+
 
   615 | :func:`YEAR(Date)`       | return the year of a date or datetime  |
 
   616 | ``YEAR(Date)``       | return the year of a date or datetime  |
 
   616 +--------------------------+----------------------------------------+
 
   617 +----------------------+----------------------------------------+
 
   617 | :func:`MONTH(Date)`      | return the month of a date or datetime |
 
   618 | ``MONTH(Date)``      | return the month of a date or datetime |
 
   618 +--------------------------+----------------------------------------+
 
   619 +----------------------+----------------------------------------+
 
   619 | :func:`DAY(Date)`        | return the day of a date or datetime   |
 
   620 | ``DAY(Date)``        | return the day of a date or datetime   |
 
   620 +--------------------------+----------------------------------------+
 
   621 +----------------------+----------------------------------------+
 
   621 | :func:`HOUR(Datetime)`   | return the hours of a datetime         |
 
   622 | ``HOUR(Datetime)``   | return the hours of a datetime         |
 
   622 +--------------------------+----------------------------------------+
 
   623 +----------------------+----------------------------------------+
 
   623 | :func:`MINUTE(Datetime)` | return the minutes of a datetime       |
 
   624 | ``MINUTE(Datetime)`` | return the minutes of a datetime       |
 
   624 +--------------------------+----------------------------------------+
 
   625 +----------------------+----------------------------------------+
 
   625 | :func:`SECOND(Datetime)` | return the seconds of a datetime       |
 
   626 | ``SECOND(Datetime)`` | return the seconds of a datetime       |
 
   626 +--------------------------+----------------------------------------+
 
   627 +----------------------+----------------------------------------+
 
   627 | :func:`WEEKDAY(Date)`    | return the day of week of a date or    |
 
   628 | ``WEEKDAY(Date)``    | return the day of week of a date or    |
 
   628 |                          | datetime.  Sunday == 1, Saturday == 7. |
 
   629 |                      | datetime.  Sunday == 1, Saturday == 7. |
 
   629 +--------------------------+----------------------------------------+
 
   630 +----------------------+----------------------------------------+
 
   630 
   631 
   631 .. _RQLOtherFunctions:
 
   632 .. _RQLOtherFunctions:
 
   632 
   633 
   633 Other functions
 
   634 Other functions
 
   634 ```````````````
 
   635 ```````````````
 
   635 +-----------------------+--------------------------------------------------------------------+
 
   636 +-------------------+--------------------------------------------------------------------+
 
   636 | :func:`ABS(num)`      |  return the absolute value of a number                             |
 
   637 | ``ABS(num)``      |  return the absolute value of a number                             |
 
   637 +-----------------------+--------------------------------------------------------------------+
 
   638 +-------------------+--------------------------------------------------------------------+
 
   638 | :func:`RANDOM()`      | return a pseudo-random value from 0.0 to 1.0                       |
 
   639 | ``RANDOM()``      | return a pseudo-random value from 0.0 to 1.0                       |
 
   639 +-----------------------+--------------------------------------------------------------------+
 
   640 +-------------------+--------------------------------------------------------------------+
 
   640 | :func:`FSPATH(X)`     | expect X to be an attribute whose value is stored in a             |
 
   641 | ``FSPATH(X)``     | expect X to be an attribute whose value is stored in a             |
 
   641 |                       | :class:`BFSStorage` and return its path on the file system         |
 
   642 |                   | :class:`BFSStorage` and return its path on the file system         |
 
   642 +-----------------------+--------------------------------------------------------------------+
 
   643 +-------------------+--------------------------------------------------------------------+
 
   643 | :func:`FTIRANK(X)`    | expect X to be an entity used in a has_text relation, and return a |
 
   644 | ``FTIRANK(X)``    | expect X to be an entity used in a has_text relation, and return a |
 
   644 |                       | number corresponding to the rank order of each resulting entity    |
 
   645 |                   | number corresponding to the rank order of each resulting entity    |
 
   645 +-----------------------+--------------------------------------------------------------------+
 
   646 +-------------------+--------------------------------------------------------------------+
 
   646 | :func:`CAST(Type, X)` | expect X to be an attribute and return it casted into the given    |
 
   647 | ``CAST(Type, X)`` | expect X to be an attribute and return it casted into the given    |
 
   647 |                       | final type                                                         |
 
   648 |                   | final type                                                         |
 
   648 +-----------------------+--------------------------------------------------------------------+
 
   649 +-------------------+--------------------------------------------------------------------+
 
   649 
   650 
   650 
   651 
   651 .. _RQLExamples:
 
   652 .. _RQLExamples:
 
   652 
   653 
   653 Examples
 
   654 Examples
 
   655 
   656 
   656 - *Search for the object of identifier 53*
 
   657 - *Search for the object of identifier 53*
 
   657 
   658 
   658   .. sourcecode:: sql
 
   659   .. sourcecode:: sql
 
   659 
   660 
   660         Any WHERE X eid 53
 
   661         Any X WHERE X eid 53
 
   661 
   662 
   662 - *Search material such as comics, owned by syt and available*
 
   663 - *Search material such as comics, owned by syt and available*
 
   663 
   664 
   664   .. sourcecode:: sql
 
   665   .. sourcecode:: sql
 
   665 
   666 
   684 
   685 
   685 - *Looking for people interested in training or living in Paris*
 
   686 - *Looking for people interested in training or living in Paris*
 
   686 
   687 
   687   .. sourcecode:: sql
 
   688   .. sourcecode:: sql
 
   688 
   689 
   689         Any P WHERE P is Person, (P interested_by T, T name 'training') OR
 
   690         Any P WHERE P is Person, EXISTS(P interested_by T, T name 'training') OR
 
   690                     (P city 'Paris')
 
   691                     (P city 'Paris')
 
   691 
   692 
   692 - *The surname and firstname of all people*
 
   693 - *The surname and firstname of all people*
 
   693 
   694 
   694   .. sourcecode:: sql
 
   695   .. sourcecode:: sql
cubicweb