Make use of sub queries
Sub queries in TQL enable you to take your queries to the next level. A sub query is a complete TQL query by itself, though it is embedded within a main query. In the selection part of the main query you can access the results of the sub query, which effectively adds an extra level of processing you would otherwise not have. We introduced sub queries briefly on the page about constraints. On the page about query variables, we showed you how to add a Safetynet counter to your solution. Let's revisit that example now to see how a sub query makes it possible to learn more about Safetynet activation across sessions.
Referring back to the Safetynet counter we added, we can run a simple query to show the total number of times the Safetynet became active per session:
1la s.sv:n:safetyNetCounter 2
The result of the query delivers raw information that, by itself, is not very helpful:
For example, you may want to know the average number of Safetynet hits across sessions or other aggregate information. Obviously, it would be extremely inefficient to compute this by hand. This is where a sub query can help us, combined with aggregate functions available in TQL. You can embed your simple query into a larger context to get the mean, minimum, and maximum value of the Safetynet counter. This is a query to achieve that:
1lu mean result.count average, 2 min result.count minValue, 3 max result.count maxValue: 4result = @(la s.sv:n:safetyNetCounter count) 5
The main query is able to look at the results of the sub query and process information about it:
Here are some additional examples showing how you can use sub queries.
This query retrieves the mean number of sessions per day in a particular month:
1lu mean x.count meanCount: x=@(d date: s.beginTime == '2021-04' , catd(model="date") s.beginTime date count) 2
If your bot uses multiple channels and you require usage information about each of the channels, you can use a query similar to this, based on the channel parameter used:
1d result.date date, result.channel channel : 2 result = @( lu s.id id, t.e.requestParameters.channel channel, date: 3 catd(model="date") s.beginTime date) 4 date 5
One strength of sub queries is that you can combine multiple sub queries into a single query, and then present results from each of them.
This example shows relative occurrences of a particular product name in a single query. You would otherwise have to run each query separately to obtain the complete results:
1la sum espresso.count, sum macchiato.count: 2 3espresso = @(ca t.id count : 4 t.e.type == "request", 5 t.e.userInput ~= ".*espresso.*"), 6 7macchiato = @(ca t.id count : 8 t.e.type == "request", 9 t.e.userInput ~= ".*macchiato.*") 10
The query generates the following result: