How to use Query Templates in order to abstract native queries?

This post explains how to use the Query Templates Management functionality that simplifies complex queries, for example, in which nested data exists. With this, when the user performs the SQL query that he created, the platform worked internally with a native query.

For this, it is necessary to use a profile with administrator role and go to the menu: Administration > Query Templates Management.

Once in the Query Templates Management screen, click on "Create":

Fill in the fields of name and description, and select the ontology in which you want to make the query.

In the sections "Selector" and "Template”, an example of how to generate the query template is shown. For this example, we are going to use the "Restaurants" ontology.

First, in the "Selector" section you have to create an SQL query with the data you want to consult. In this case, we will find the restaurants located between two coordinates (latitude and longitude):

select * from Restaurants where lat > @x and lon < @y

In this case, for example, this would give you all restaurants that are both north of latitude @x, and west of longitude @y.

Variables can be assigned any name, but their values must always be identified by preceding them with an @.

In order to use this SQL query that has been created, it will be necessary to put the corresponding native query in the "Template" section:

var lat = @x
var lon = @y
var query = 'db.Restaurants.find({"Restaurant.address.coord.0": {"$gt": '+lat+'}, "Restaurant.address.coord.1": {"$lt":'+lon+'}})';

return query;

In this way, when we want to make a query with these values, from the query tool (Tools > Query Tool), we will copy the SQL query generated and add the values that we want.