MQL reference

MQL syntax

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.

SELECT 'iteration'

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

WHERE Status IS NULL
WHERE Status = NULL
WHERE Status IS NOT NULL
WHERE NOT Status = NULL
WHERE Status != NULL

Aggregation

Where aggregation functions (AVG, 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.

SELECT "iteration", count(*)

If your database supports aggregation functions other than AVG, COUNT, and SUM you may try them, but we cannot guarantee that they will work. Please be aware that MQL GROUP BY and ORDER BY work in the same way as SQL GROUP BY and ORDER BY. Please refer to SQL on how to use this.

Numeric and date comparisons between properties

Numeric and date property values, if they are of the same property type, can be compared with one another.

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.

ORDER BY

The ORDER BY keyword is used to sort the result-set by a specified column. It sorts the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.

Example:

table query: select number, name WHERE type = story ORDER BY name

MQL usage

This section is designed to explain how to use MQL in specific circumstances with specific examples.

Managed Properties

Property names can always be quoted using single quotes. Property names containing spaces must always be quoted with single quotes.

SELECT 'iteration', count(*) WHERE 'story status' = 'open'

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 AVG 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

List property values, similar to property names, can always be quoted using single quotes. Values that contain a space though, must always be quoted with single quotes. If you use any of the greater than or lesser than operators with a list property value, the positional ordering of the values is used to compare them.

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 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

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.

Use card number - NUMBER

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 cannot combine these so for example,

WHERE type = story AND 'Iteration' = '#72 Iteration 1'

will not work.

Compare multiple values - IN and NUMBERS IN

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.

Beta: Compare multiple values - Nested IN

Mingle 3.3 contains a beta feature to support nested IN MQL statements. This allows you to query for a set of cards based on conditions for a related card.

What's a beta feature?

Beta features are features which have not been fully integrated into Mingle, but which we believe provide enough value, even in an introductory state, to be included as part of the release. The nested IN feature has been available in MQL since Mingle 2.0. This feature was intended for internal use only - it is the mechanism behind the tree filters. However, because MQL is similar to SQL, some users have discovered that MQL supported nested IN statements. Since people are using this feature, and find it valuable, we decided to begin publicizing it more broadly. As with all beta features, there are limitations to this feature. See the specific limitation warnings below.

To compare the value of a relationship property with multiple values, you can use the nested IN operator with number:

where type = task and 'release - story' IN (SELECT number WHERE type = story AND status = 'in development')

where 'release - story' is a tree relationship property name. This query will return all tasks which are related to stories that are 'in development'.

Generally, you will want to use number as the property in the nested IN. However, you can also use name or related properties:

where type = task AND 'release - story' IN (SELECT name WHERE type = story AND tagged with 'potential 3.3')

where 'release - story' is a tree relationship property name. If there are stories with the same name, this will include the tasks for both stories even if only one of the stories is tagged with 'potential 3.3'. As mentioned above, this is rarely the desired behavior and you will want to use SELECT number rather than SELECT name in the nested IN.

Limitations

This feature has only been tested for use with the table query macro and the execute_mql API. Other usages of nested IN in MQL may work. However, when using this feature in other areas, make sure to save your work beforehand, as the behavior is untested and the results undefined.
There is a known limitation with advanced MQL filters on card views. When Mingle metadata, e.g., properties, card types, etc., are modified these changes are reflected in saved views. For example, suppose a saved view has an advanced MQL filter of:
```
'status' = 'in development'
```
If the 'status' property is renamed to 'story status' the advanced MQL filter for the saved view will change to:
```
'story status' = 'in development'
```
Now suppose instead that a saved view has an advanced MQL filter of:
```
'depends' IN (SELECT number WHERE 'status' = 'in development')
```
If the 'status' property is renamed to 'story status' the advanced MQL filter will not change and will be in an invalid state - since the 'status' property in the nested IN is unrecognized. You will receive an error message on the saved view and will be able to change it manually.

Use THIS CARD

You can use the THIS CARD keyword to specify a property's value as the current card when you are using MQL in any card description.

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 example of where THIS CARD macro is useful is in card defaults. If you use the 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.

THIS CARD is not a valid keyword for use in MQL on pages, via the API, the MQL filter or conditional aggregates.

To see THIS CARD in use in card defaults take a look at the Agile hybrid template Release and Iteration card defaults.

Use project variable

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.

Mingle does not support > and < for comparison of relationship properties.

Predefined properties

Predefined properties are special properties which are automatically assigned to any card. "Type" is a text property, "Name" is a text property, "Number" is a numeric property, "Created On" is a date property, "Modified On" is a date property and "Project" is a text property. These six predefined properties can be used in MQL.

Project can only be used in MQL select statements such as

SELECT project

Using it in MQL conditions such as

WHERE Project = my_project

is prohibited.

Finding cards in a tree

The FROM TREE syntax can be used to select cards from a specific tree. The FROM TREE syntax must be used before the conditions WHERE clause.

SELECT name, number FROM TREE 'Planning' WHERE Type = Story

The FROM TREE syntax can be used in the MQL filter in the following format.

FROM TREE 'Release Planning' WHERE Type = Iteration AND Release = (Current Release)

You cannot select more than one tree using the FROM TREE syntax.

Finding cards with tags

MQL can be used to find cards with certain tags.

table query: SELECT number, name WHERE TAGGED WITH 'bug'

Multiple tags can be specified to further restrict the query.

table query: SELECT number, name WHERE TAGGED WITH 'bug' AND tagged with 'open'

MQL can be used to find cards without certain tags.

table query: SELECT number, name WHERE NOT TAGGED WITH 'Image'

How to use MQL special keywords

TODAY and CURRENT USER are special keywords that are available for date and user properties. The examples below show their correct usage in MQL.

WHERE 'Fixed Date' IS TODAY
WHERE 'Fixed Date' = TODAY
WHERE Owner IS CURRENT USER
WHERE Owner = CURRENT USER

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.

How to access values of properties on a card

Using THIS CARD.property in MQL

THIS CARD.property is not valid for use in MQL on wiki pages, via the API, the MQL filter or conditional aggregates.

THIS CARD.property (where property is any property name) can be used in a MQL WHERE clause to access values of properties on the card where THIS CARD.property syntax is used. This allows people to create reports on cards that are automatically updated when the card properties are updated. This is also useful when creating reports on card defaults as the reports can be created in the generic form using THIS CARD.property but when a card is created from the default the card's reports will automatically be updated with the card's specific values.

Show the card that I depend on
```
table query: SELECT NAME, NUMBER WHERE NUMBER = THIS CARD.'Depend on'
```
Where 'Depend on' is a card relationship property, this will show the card which the current card depends on.
Show the cards that depend on the same card that I depend on
```
table query: SELECT NAME, NUMBER WHERE "Depend On" NUMBERS IN (THIS CARD.'Depend on')
```
Where 'Depend on' is a card relationship property, this will show all cards which depend on the same card which the current card depends on.
Other examples
```
table query: SELECT number, name WHERE status = THIS CARD.status
```
This would create a table of cards that had the same value for the status property as the card this query was used on. For example: if the card had the value 'open' then this table would show all cards that were open.
```
value query: SELECT "start date" WHERE "start date" = THIS CARD."start date"
```
This would print out the value of the start date on the card where this query was used.
```
table query: SELECT number, name WHERE "revision reported" > THIS CARD."revision completed"
```
This would create a table of cards that has a value for the "revision reported" property that is greater than the "revision completed" on the card where this query is used.

Using THIS CARD.property in macros

You can also use THIS CARD.property (where property is any property name) as a value for most parameters on the standard macros. Any property data types which match the types of any chart parameters can be used in THIS CARD.property to generate charts. For example, you can use THIS CARD."Start Date" as the value of the parameter 'x-labels-start' on the data series chart. This will restrict the x-axis to start on the value of the "Start Date" property on the card where it is used.

Beta: Using AS OF in MQL

Mingle 3.2 supports a beta feature to query historical card information. Using this feature, you can see what the final values of a card property are at the end of a day.

What's a beta feature?

Beta features are features which have not been fully integrated into Mingle, but which we believe provide enough value, even in an introductory state, to be included as part of the release. In particular, with the introduction of the daily history chart, we wanted to provide a way to verify and analyze the chart data, which querying historical card data allows. See the specific limitation warnings below.

Example Data

The easiest way to understand querying historical card information is to see an example. The example below runs against a Mingle project with the following card data:

At the end of July 12, 2010, the project contains the following cards:

Number	Estimate	Status
1	2	Open
2	8	Open

On July 13, 2010, card #3 was added and there were some changes made to the existing cards:

Number	Estimate	Status
1	5	Open
2	8	Closed
3	1	Open

MQL 'AS OF' date

In Mingle 3.2, you can now execute MQL "AS OF" a certain date. For example, running against the example data above, the following MQL query:

SELECT Number, Estimate AS OF "July 12, 2010" WHERE Status=Open

will yield the following results:

Number	Estimate
1	2
2	8

Some things to notice:

AS OF "July 12, 2010" is between the SELECT clause and the WHERE condition.
The values that are returned are the values as of July 12, 2010. Notice in the example data above that the Estimate for card #1 was changed from "2" to "5" on July 13, 2010. The query results show that card #1 has an Estimate of "2", which was the case on July 12, 2010.
The WHERE condition is compared against the card data as of July 12, 2010. Notice in the example data above that the Status for card #2 was changed from "Open" to "Closed" on July 13, 2010. The results include card #2, because it had a status of "Open" on July 12, 2010.
Cards that have been added after the "AS OF" date aren't included, even if they match the WHERE condition. Card #3 has an "Open" Status on July 13, 2010. However, the card did not exist at the end of July 12, 2010, so it is not included in the results.

As noted above, querying historical card information is a beta feature and is not fully integrated into Mingle. The only place that we have specifically intended the "AS OF" functionality to work is in the table query macro. For example,

table query: SELECT Number, Estimate AS OF "July 12, 2010" WHERE Status=Open

is how you would use the query examples above.

Most importantly, we have not tested where the "AS OF" functionality does not work. The behavior of "AS OF" in any other area of Mingle is officially "undefined" - meaning that you may or may not get the results you expect. If you attempt to use "AS OF" in any other area, make sure you have saved any data you don't want to lose.

Regular MQL without an "AS OF" clause, continues to work in Mingle 3.2. For example, running against the example data above, the following MQL query:

SELECT Number, Estimate WHERE Status=Open

will yield the following results:

Number	Estimate
1	5
3	1

This is standard MQL that has always been available in Mingle.

Limitations

Querying card history has some limitations that regular MQL does not have. These limitations are due to the historical information that is available in Mingle. When you use "AS OF" in a MQL statement, you will not be able to use the following:

FROM TREE to select cards from a tree.
Tagged with to select cards with tags.

Help documentation

MQL reference

MQL syntax

SELECT

Conditions / WHERE

How to form negative conditions

Aggregation

Numeric and date comparisons between properties

ORDER BY

MQL usage

Managed Properties

Unmanaged Properties

Property values

User properties

Date properties

Relationship property values

Predefined properties

Finding cards in a tree

Finding cards with tags

How to use MQL special keywords

How to access values of properties on a card

Using THIS CARD.property in MQL

Using THIS CARD.property in macros

Beta: Using AS OF in MQL

Example Data

MQL 'AS OF' date

Limitations