TQL stands for Tuleap Query Language. It is the most powerful way to search for artifacts in Tuleap. Want more? TQL is for all: developers, agile people, project leaders, businesses. This article intends to give you examples and tricks to help you faster create valuable reports.
What Tuleap Query Language does
Tuleap Query Language (TQL) is the newest addition to Tuleap trackers. It is a custom language used on Tuleap trackers to let you select artifacts according to whatever criteria you choose. TQL is inspired by SQL, the language used to retrieve information from databases. Much like SQL, TQL lets you create very specific queries, so you see only those artifacts that are relevant to your needs.
How to use Tuleap Query Language?
You can use TQL in two areas: in Tuleap Tracker reports and in Tuleap REST API.
Query Tuleap Trackers
You can use it to create a tracker report. Click the “expert mode” button to display a text area where you can write a query. Once you switch to “expert mode” you can switch back to “normal mode” at any time. The criteria you set will not be changed.
On the right hand side, you’ll see a select box that contains all the fields available for query. When you write TQL queries, you must use fields’ names. A field’s name can sometimes look very different from its label. When you see fields in reports, it’s always the field label that is displayed.
For example, on tuleap.net, the label “Original Submission” corresponds to the name “details”. When you click on a field label, its name will be inserted where your cursor is in the field—a very handy feature!
TQL queries are always made of comparisons. A comparison is a field name followed by an operator and a value. For example, submitted_by = “jmasson”. There are several operators available like =, BETWEEN(), >=, <. Some operators are only available for certain fields. Comparisons can be combined with the keywords AND, OR and parentheses (). This enables you to create very specific or very broad selections.
All your advanced queries done with TQL update your charts, barrs and cardwalls you create as well
Please check Tuleap documentation for full details on fields, operators, and query errors.
You can also use autocomplete when writing queries CTRL + SPACE. You’ll see a list of suggested field names and operators. When there is only one possibility (like if you write BETW), autocomplete will write the operator BETWEEN(.
Query REST API
You can also use Tuleap Query Language in the REST API to search for artifacts of a tracker:
GET /trackers/{id}/artifacts
You will need to provide the tracker’s ID and the TQL query in the expert_query parameter. Note that you can only use either the “query” or the “expert_query” parameter here.
How is TQL different from the existing tracker reports?
First, you can search for artifacts that would be impossible to find using the existing Tracker report interface (“normal mode”). Plus, TQL supports “dynamic values”. This lets you create reports that automatically adapt to the user viewing them or to the current date.
Let’s try using the Request tracker on Tuleap.net, the public Tuleap platform. Let’s say we want to retrieve all bug reports submitted over the last month by the person running the query. Let’s say that today is March 7, 2017, or “2017-03-07”.
This query looks and works much like what you would see using the Tuleap Reports user interface:
submitted_by = "jmasson" AND open_date >= "2017-02-07" AND open_date <= "2017-03-07"
This query works today, but what about tomorrow? Or a month from now? If we want to reuse it at a later date, it has to be rewritten:
Example:
submitted_by = "jmasson" AND open_date >= "2017-03-07" AND open_date <= "2017-04-07"
This is not ideal by any means. However, it is the only way to do it via the Tuleap Reports user interface. TQL, on the other hand, lets us tweak the query in several ways to make it smarter and better! With TQL, we have access to a dynamic value that represents the current date and time: NOW():
Search with Dynamic Value
submitted_by = "jmasson" AND open_date >= NOW()
There is only one problem: open_date >= NOW() means “artifacts that were submitted later than now.” So, artifacts that were submitted in the future…Well, there aren’t any yet! TQL has many great features, but time travel isn’t one of them (yet!). Fortunately, there is a fix for this. We can apply modifiers to NOW() and add or subtract days, weeks, months, or years. Here, we want last month so we’ll subtract one month from NOW():
submitted_by = "jmasson" AND open_date >= NOW() -1m AND open_date <= NOW()
If today is March 7, 2017, the query is for all artifacts submitted later than February 7, 2017 and earlier than March 7, 2017. And, with TQL, our query is dynamic, so it will automatically adapt to the current date. In a month, the same query will select artifacts between March 7, 2017 and April 7, 2017. And, since artifacts cannot have an open_date > NOW(), which would be future artifacts, the last part of the query is not necessary, making it even simpler:
submitted_by = "jmasson" AND open_date >= NOW() -1m
I entered “jmasson” to find which bugs I had submitted. But if I save this report and someone else sees it, they will see my bugs, not theirs.
TQL to the rescue again with another dynamic value: MYSELF():
submitted_by = MYSELF() AND open_date >= NOW () -1m
Search with MYSELF Value
MYSELF() lets you write queries that change based on who is running them. Assuming that I am currently logged in with my username “jmasson”, the query will select all artifacts that “jmasson” submitted. If my teammate Juliana logs in and goes to the same tracker report, the same query will select all artifacts that “juliana” submitted.
You must be logged in to Tuleap to see MYSELF() query reports. If you are not logged in, you will get an error message.
Search with no value
With TQL, you can also select fields that have no value, either because the value was never set, or because a user selected “none”. In “normal mode” it is not possible to search fields that have no value.
Let’s select user stories that we haven’t estimated yet:
initial_effort = “”
This also works for dates. Let’s say that I am working on support requests along with other team members. We use date fields to mark a ticket as acknowledged and resolved (and can be closed). We want to see which requests are acknowledged but not yet resolved, so that we can work on fixing these problems:
acknowledged_date != “” AND (resolved_date = “” OR status != “Closed”)
Tips and tricks
- Hitting
CTRL + ENTERwill submit the query. This is very useful when you want to quickly enter queries without having to click the “Submit” button each time. - Hitting
CTRL + SPACE” will autocomplete operators and field names. - Use
IN()instead of many=for list fields:Example :
status = “On going” OR status = “Blocked” OR status = “Review”
should be written as below:
status IN (“On going”, “Blocked”, “Review”)
- Use
BETWEEN()instead of two date comparisons:Example:
open_date >= now() - 1w AND open_date <= now()
should be written as below:
open_date BETWEEN( now() - 1w, now()
Time waits for nobody
In Tuleap Trackers, date fields can be configured to store the date and time: “2017-03-07 14:31”, for example. We will call these “datetime” fields. They can also be configured to only store dates, for example “2017-03-07”. We will call these “date” fields.
This difference matters in TQL. When you search on a date field, TQL will only accept “YYYY-MM-DD” date formats. When you search on a datetime field, TQL will accept the datetime format “YYYY-MM-DD HH:mm”. Datetime fields will also accept the short date format “YYYY-MM-DD”.
When comparing a date or datetime field to the short date format, depending on the operation, the value “2017-03-07” can be interpreted as “2017-03-07 00:00:00” or “2017-03-07 23:59:59”. The difference between the > operator and the >= operator is that the stricter > transforms the date “2017-03-07” to “23:59:59”. The more permissive >= operator will also select artifacts on a given date, so it will round the value to “00:00:00”.
For example, date_field > “2017-03-07” means date_field is later than 2017-03-07 23:59:59, while date_field >= “2017-03-07” means date_field is later than or equal to 2017-03-07 00:00:00.
Please refer to Tuleap documentation for more details on date fields in TQL.
List fields bound to user groups
When you configure list fields in Tuleap Trackers, you can bind their values to user groups. These user groups can be defined by type of user (“Developers”, for example), or they can be defined by the system. Tuleap manages several user groups by default, for example “Project members”, “Registered users”, and “Project administrators”. Did you know that these user group names are translated into the user’s language?
In TQL, you can search for these list values using each translation. For example, if you use the French translation, the following queries will return the same artifacts, regardless of the user’s language:
list_field = “Project members” list_field = “Membres du projet”
What’s next?
Tuleap Query Language is currently only usable on artifact fields. What if we could search on artifact follow-up comments? Imagine being able to find all the support requests you commented on over the last week.
Tuleap Query Language for follow-up comments has already been estimated by the Tuleap Core developer team. See the public user story on tuleap.net for more information.
Support for other fields in TQL is not planned yet. Get in touch with us if you would like to see more fields added!
In an other article, we talk about the technologies involved in building TQL. We hope you enjoy it!
Install latest Tuleap
Join Online meetings