Multidatasources-datadiscovery queries in Gadget Template

Owned by DevPortal Editor (Unlicensed)
Last updated: 24/01/23 by Onesait Platform
5 min read

EN | ES

Sometimes, to create a gadget template that meets your needs, you need to be able to access more than one datasource. For this purpose, some methods have been implemented:

      vm.getDataFromDataSource(datasource,callbackFunction,filters,group,project,sort,limit,offset,param,debug)

The first three parameters are available in versions older than empire, but the others are new in this version. All these operations are applied on the server by pushing the logic down to the database.

  • datasource: identifier of the datasource to which you want to access. Bear in mind that this will only return data to which the user has access. If you want to display a public dashboard, then the ontologies from which the datasources are formed, must also be marked as public
  • callbackFunction: function that will return the data after the query. When defining it, it only needs a parameter, which will be the one that returns the data.
  • filters: you can pass an array of filters for the data. An example of filters would be this one, where two of them are passed: [{field: f.field1,op: "=",exp: f.value1},{field: f.field2,op: "=",exp: f.value2}], where field is the path of the datasource parameter, for example 'Helsinki.year', op is the operator and can be =,>, <, ... and exp is the value.
    • IN filter example: If you have an array of strings with the elements by which you want to filter, you can create an array that contains all the filters in case you use more than one, and then pass this variable to the query, for example:
var SelectedItems = ['item1', item2 ',' item4 '];

var filters = [];

filters.push ({field: 'fieldOfTheEntityByFilter', op: "IN", exp: "('" + selectedelements.join ("', '") + "')"});

In the case that they were numerical values, we would not put the single quotes.

vm.from (datasource) .filters (filters) .exec ...
  • group: array of string for the grouped fields in the datasource. To be consistent, these fields must also be in the project stage. If not needed (but if there are subsequent parameters) the value can be passed empty []
  • project: indicates the projection applied to the datasource, and consists of a JSON array of two fields {"field":"field/projecttop","alias":"alias"} defining each projection with its alias (not required), for example [{"field":"Restaurant.cuisine"}] or [{"field":"Restaurant.cuisine","alias":"cui"}]. If there are group-type fields, those should go as projection. If not needed (but if there are subsequent parameters) the value can be passed empty []
  • sort: indicates the order that is applied to the datasource, and is made up of a JSON array with two fields {"field":"fieldtosort","asc":true/false}. For example, a valid descending order is this: [{"field ":"Restaurant.cuisine","asc":false}]. If not needed (but if there are subsequent parameters) the value can be passed []
  • offset: indicates the offset applied to the datasource. It is an integer value. If not needed (but if there are subsequent parameters), the value -1 can be passed.
  • limit: indicates the limit applied to the datasource. It is an integer value. If not needed (but if there are subsequent parameters), the value -1 can be passed.
  • param:parameters can be included in the datasource to, for example, replace a value within a subquery or any the part that is required. Bear in mind that this parameter enables a SQL injection, so it must be used in a controlled manner. The parameter in the datasource will be defined with the notation {$param}, for example:
SELECT t1.pt as pt
FROM
(select * from translations) as t1,
(select * from translations) as t2
where t1.{$cdest}=t2.id

It is made up of a JSON array with two fields {"field":"paramtoverwrite","value":"value"}, for example: [{"field":"cdest","value":"United States"}]

  • debug: only enabled for the development mode (for the visualization, it will have to be disabled). Defaults to false. If the engine response is enabled, it returns the generated query for debugging.


We will now make an example of use:

For starters, create two datasources, one with the HelsinkiPopulation ontology and the other with the Restaurants ontology:

In a dashboard, create a gadget template.

Access the gadget edition:

and write this in the HTML text box:

<!-- Write your HTML <div></div> and CSS <style></style> here -->

<!--Focus here and F11 to full screen editor--> 

<h2>HELSINKIDATA </h2>
{{vm.helsinkiData}} 

<br><h2>RESTAURANTSDATA </h2>
{{vm.restaurantsData}}


and this in the JAVASCRIPT text box:

//Write your controller (JS code) code here

//Focus here and F11 to full screen editor


//This function will be call once to init components
vm.initLiveComponent = function(){   
   vm.getDataFromDataSource('helsinki',vm.callbackFunctionHelsinki,[{field:'Helsinki.year',op:'=',exp:'2000'}]);
   vm.getDataFromDataSource('restaurants',vm.callbackFunctionRestaurants);
}; 


vm.callbackFunctionHelsinki = function (data){ 
  vm.helsinkiData = data;
  console.log(vm.helsinkiData);
}

vm.callbackFunctionRestaurants = function (data){ 
  vm.restaurantsData = data;
  console.log(vm.restaurantsData);
}

//This function will be call when data change. On first execution oldData will be null
vm.drawLiveComponent = function(newData, oldData){
};

//This function will be call on element resize
vm.resizeEvent = function(){

}

//This function will be call when element is destroyed
vm.destroyLiveComponent = function(){

};



If you want to pass a string in the filter, pass it escaping the quotes in this way: exp: '\' TEXT \ ''

To make a filter on dates, you can use this format: [{field: 'DATE', op: '<=' , exp: "TIMESTAMP('2011-01-02T00:00:00.000Z')"}]


Datasource API Chaining and operations with promise (from version Empire):

From version 1.6.0-Empire onward, the possibility of using a more agile API, based on promises, is available. From any template, you have the following operations:

  • vm.get('datasource',params): it only requires the datasource's id. Params will be a JSON structure with all operations embedded, for example:

{"limit":3,"offset":4}

This method returns a promise, so that you can operate with it, for example:

vm.get("DsRawRestaurants").then(
data => console.log(data)
)

or (if backward compatibility with older browsers is required):

vm.get("DsRawRestaurants").then(
    function(data){
        console.log(data)
    }
)
  • vm.getOne('datasource'): special case of above (vm.get(datasourcename,{"limit":1})) where you only want one value.
  • vm.from('datasource')...: with this function, you access the Api Chaining of the datasource that also returns a promise. It has this structure:

vm.from(datasource).(chaining operations).exec();

The "chaining operations" correspond to all the operations on the datasources, and are used to compose the parameter structure in a simple way. Operations needing an array type, can be composed either by passing the parameter array, or by adding one or more stages to it. The order is irrelevant, the only thing to take into account is the origin "from" and the destination "exec/execute". For example:

vm.from(vm.datasource.identification).filter(getDataStatusFilters()).group(vm.getGroupFields()).select(vm.getSelectFields()).sort(vm.getSortFields()).exec();

The "chaining operations" allowed are the following: from, where (filter), offset (skip), limit(max), group, project(select), sort, param, debug, exec (execute)

An example with a restaurants datasource, of the type:

vm.from("restaurants")
	.project([{"field":"Restaurant.cuisine","alias":"cui"},{"field":"Restaurant.cuisine","alias":"coun","op":"count"}])
	.group("Restaurant.cuisine") //or you can put several like ["Restaurant.cuisine","Restaurant.borough"] 
    .exec()
    .then(
		function(data){
			vm.data = data;
		});

and in the html you can write {{vm.data}} to see the result of the query.