Query as Widget Datasource

Question

I'm trying to define a widget's datasource as the result of a query, but I'm not sure if it's posible.

I'm working with SQL views and a Table, I'd like to show the values of the IDs that I have on the table that come from the views.

function queryValue(source, model, key){
  console.log("source " + source);
  app.datasources[model].query.filters.id._equals = source;
  app.datasources[model].load(function () {
    console.log(app.datasources.users.items[0][key]);
    return app.datasources.users.items[0][key];
  });
  app.datasources[model].query.clearFilters();
}

Calling it like:

queryValue(@datasource.item.[the_id], "[the_SQLView_Datasouce]", "[the_field_i_want]");

In this case the widget is a table, so the fucntion would repeat the amount of items in the talbe

The problem is that either I get the same result as meny times as the amount of items or the first one does not work!

And the second problem is that the result does not over write the widget text to show.

It's a very simple function and I did found some workarounds but not with the datasource feature and they work too slowly, any sugestions? Is it possible to do such thing with the datasource?

From the screenshot it looks like you are trying to display ID of the relation. If you have two models(tables) like Employee and Manager or you have self-referenced Employee model(table) then you can just add prefetch for your datasource. In this case you will be able to bind Manager Id like this: @datasource.item.Manager.Id. Docs: https://developers.google.com/appmaker/models/datasources#prefetch — Pavel Shkleinik, Jan 23 '17 at 21:32
Since it's an SQL view it doesn't have a relation, so I have to query by hand =) — Juan Diego Antezana, Jan 24 '17 at 09:38
In this case, If the table is view-only, I would suggest using calculated datasource instead. This will reduce number of calls to server and database, so you'll improve overall page performance and get rid off labels blinking. With current implementation, for table with N rows app will make at least (N + 1 calls to server) + (N + 1 calls to database). With calculated datasource you will reduce it to 1 + 1 = 2 calls. — Pavel Shkleinik, Jan 24 '17 at 22:01
@Juan How to import sql views? I understand that I can create a view using cloud shell. https://developers.google.com/appmaker/models/cloudsql#views But, I see no option in appmaker to import it. How did you do it? — TheMaster, Apr 18 '19 at 09:19

score 2 · Accepted Answer · answered Jan 23 '17 at 18:25

If I understand the question correctly, you probably want to do the query on the server side. The issue with the sample code posted is it's triggering a load on a single data source multiple times before any of the loads can return. When this is done, the data source is only loaded with the results from one of the loads, I believe the last one. So you're probably seeing the results from the last query you did for all your callbacks.

So instead your code should instead be a server side script, and should be something like:

function queryValue(source, model, key){
  console.log("source " + source);
  var query = app.models.newQuery();
  query.filters.id._equals = source;
  var results = query.run;
  return results[0].key;
}

(Written from memory, so pardon any errors.)

Thanks again Devin! After some minor tweeks the solution works! — Juan Diego Antezana, Jan 24 '17 at 11:19

score 2 · Answer 2 · edited Feb 01 '17 at 19:48

Following Devin's suggestion:

Front-end

/*****************************************************************************
Front-end function that calls the querying function @queryValue(source, model, key) in controller_TransformId
@source => the field ID to transform to label
@model => the model name to be queried
@key => the label to be acquired with the query
@wwidget => the widget making the request
This function works as a model to manage the transactions between the 
controller at the backend and the view.  
******************************************************************************/
function buildTransformID(source, model, key, widget){ 
  google.script.run.withSuccessHandler(
    function successHandler(expectedValue){
      widget.text = expectedValue;})
  .withFailureHandler(
    function failureHandler(){
      widget.text = "undefined";})
  .queryValue(source, model, key);
}

Back-end

/*****************************************************************************
Back-end function that queries the database
@source => the field ID to transform to label
@model => the model name to be queried
@key => the label to be acquired with the query    
This function works works as a controller to query the database from the backend    ******************************************************************************/
function queryValue(source, model, key){ 
  var query = app.models[model].newQuery();
  query.filters.id._equals = source;
  var results = query.run();
  console.log("CONTROLLER return :" + results[0][key]);
  return results[0][key];
}

Is it mandatory to pass through the widget.text value? the successHandler callback is asynchronous so regular returns would just give me nulls

Query as Widget Datasource

2 Answers2