MQL reference
MQL is SQL-like in its syntax, but supports a rather specific subset of SQL functions and, we hope, is much simpler. While you should be inspired to use your knowledge of SQL to try things in MQL, please do not assume that anything that works in SQL will work in MQL. For example, you cannot do a JOIN using MQL.
SELECT
Many of the macros allow/require you to specify the set of cards you wish to render using a MQL SELECT statement. A MQL SELECT is very similar to a SQL SELECT except that you are selecting card properties rather than database columns.
Conditions / WHERE
A WHERE clause can be added to any SELECT statement to restrict the set of cards. Where a macro calls for only a conditions clause, the inclusion of the WHERE keyword is optional.
In a condition clause the following operators are supported:
* =
* IS
* !=
* NOT
* >
* >=
* <
* <=
* IN (...)
* AND
* OR
* ( ... )
How to form negative conditions
The following is the syntax for checking the value of a property which has not been set
How to use TODAY and CURRENT USER keywords
TODAY and CURRENT USER are special keywords that are available for date and user properties. Below examples are the syntax to represent correct usages of them in MQL.
Using single quotes or parenthesis around TODAY or CURRENT USER may not return correct result.
Anonymous users cannot see charts created using CURRENT USER keyword.
Aggregation
Where aggregation functions (AVERAGE, COUNT, SUM) are supported you can select an aggregation function as one of your result set columns. If you are selecting only a single column and it is an aggregation, you must also specify a GROUP BY clause.
If your database supports aggregation functions other than AVERAGE, COUNT, and SUM you may try them, but we cannot guarantee that they will work.
Card type
Card types work exactly like any other list property in MQL. The property name is Type. Certain operations, like lesser than and greater than, that do not make sense for a card type are not supported.
Managed Properties
Property names can always be quoted using single quotes. Property names containing spaces must always be quoted with single quotes.
Unmanaged Properties
Free text properties can be used in a manner similar to managed properties except there is no obvious concept of ordering in a text property. If a free text property is used to store purely numeric values, you can use the property in aggregate operations like AVERAGE and SUM as well. Default comparison for text property values is alphabetic. On the other hand, default sort order is numeric if the property contains purely numeric values and you are doing a numeric comparison.
Property values
{{ table query: SELECT number, name, status WHERE Type = Feature AND Risk < Medium }}
User properties
When using user properties in MQL, the user's login should be used as the value. Operations such as greater than and lesser than are not supported for user property values. In addition, to specify a value representing the currently logged in user, use the CURRENT USER keywords.
{{ table query: SELECT number, name, status WHERE Owner = CURRENT USER }}
Date properties
All properties, except numeric ones can be compared to a date. Mingle is lenient in interpreting dates and does not restrict you to the project's date format. The newly introduced date properties will behave the same way. Operators such as greater than and less than work as expected in the context of dates. In addition to this, to specify a value representing the current day in the project time zone, use the TODAY keyword. Similar to user properties, you can also use special keywords like IS TODAY and IS NOT TODAY to express your intent more clearly.
{{ table query: SELECT number, name WHERE 'Completed On' IS TODAY }}
The project time zone is used to determine TODAY.
Relationship property values
There are two types of relationship properties - Card Relationship Property and Tree Relationship Property. Both of these relationship property values are cards. There are various ways to specify a card as a value of these properties.
- Use card name
- Use card number - NUMBER
- Compare multiple values - IN and NUMBERS IN
- Use THIS CARD
- Use project variable
where type = story and 'Iteration' = 'Iteration 1'
where 'Iteration' is the tree relationship property name and 'Iteration 1' is the name of the iteration card.
If we assume that the card with name 'Iteration 1' has card number 72, the above MQL could also be written as follows:
where type = story and 'Iteration' = NUMBER 72
where 'Iteration' is the tree relationship property name and 72 is the number of the iteration card. This syntax could be useful to disambiguate cards which have the same name, e.g. you could have two cards with the same name, 'Iteration 1' in two different releases.
To reference a card as the value of a relationship property you must either use NUMBER or the card name. You can not combine these so for example,
where type = story and 'Iteration' = '#72 Iteration 1'will not work.
To compare the value of a relationship property with multiple values, you can use the IN operator with either the name or number:
where type = story and 'Iteration' IN ('Iteration 9', 'Iteration 10', 'Iteration 11')
where 'Iteration' is the tree relationship property name and 'Iteration 9', 'Iteration 10' and 'Iteration 11' are the names of iteration cards.
NUMBER IN or NUMBERS IN are also supported to compare multiple values.
where type = story and 'Iteration' NUMBERS IN (71, 72, 73)
where 'Iteration' is the tree relationship property name and 71, 72 and 73 are numbers of cards of type Iteration.
You can use the THIS CARD keyword to specify a value representing the current card you are on from any card editor.
where type = story and 'Depend on' = THIS CARD
where the 'Depend on' is the card relationship property name and THIS CARD is pointing to the card this keyword is being used on. One of example where THIS CARD macro is useful is in card defaults. If you set above MQL to create a chart in an Iteration card type default, then when a new Iteration card is created this chart will refer to the current card created. Note that THIS CARD macro is not a valid keyword for wiki page.
To see THIS CARD in card defaults, take a look at the Agile hybrid template (2.1) Release and Iteration card defaults.
where type is story and 'Iteration' = ('Current Iteration')
where Iteration is tree relationship property and 'Current Iteration' is name of the project variable. Note that the parenthesis should be used around the project variable name. In this example, the user created the project variable with the name as, 'Current Iteration', using single quotes. So in MQL syntax put parenthesis around the entire given project variable name.
Numeric comparisons between properties
{{ table query: select number, name WHERE 'Due date' < PROPERTY 'Date completed'}}
{{ table query: select number, name WHERE 'Actual Effort' > PROPERTY 'Estimate'}}
Please note that keyword PROPERTY is used to reference second property for comparison.
Tags
MQL can be used to find cards with certain tags.
{{ table query: SELECT name, number WHERE TAGGED WITH 'bug' }}
Multiple tags can be specified to further restrict the query.
{{ table query: SELECT name, number WHERE TAGGED WITH 'bug' AND tagged with 'open' }}
Tags can be used in a negative syntax.
{{ table query: SELECT name, number WHERE NOT TAGGED WITH 'Image' }}