<custom> chart : how can I access the data in the SQL query?

[willy-dauphin]

Hello,
I'd like to access the results of my SQL query via javascript to create a custom chart. I've looked through the four variables presented (element, x, ys and Plotly) but can't find anything. Any ideas?

I tried with a very simple query with similar column names but... that didn't work either!

SELECT 'Category A' AS x, 10 AS ys UNION ALL SELECT 'Category B' AS x, 20 AS ys UNION ALL SELECT 'Category C' AS x, 15 AS ys UNION ALL SELECT 'Category D' AS x, 25 AS ys UNION ALL SELECT 'Category E' AS x, 30 AS ys

Thank you very much and have a nice day :)

[willy-dauphin]

My Redash configuration seems fine. However, I'm getting this error in the javascript console on each of my pages. Maybe it's related.

[yoshiokatsuneo]

I'm not sure but it looks the error is about CSP on Firefox. But, I cannot reproduce the issue on Firefox.
I guess you can just check CSP settings on Firefox, or try to use Chrome.

Thank you !

[willy-dauphin]

Thanks for your answer. Unfortunately, there is still troubles even if my CSP configuration is now good.

Indeed, I add this line to my env file : REDASH_CONTENT_SECURITY_POLICY = "default-src 'self'; style-src 'self' 'unsafe-inline'; script-src 'self' 'unsafe-eval' 'unsafe-inline'; font-src 'self' data:; img-src 'self' http: https: data: blob:; object-src 'none'; frame-ancestors 'none'; frame-src redash.io;",

The solution was to transform this default configuration : script-src 'self' 'unsafe-eval';
to this : script-src 'self' 'unsafe-eval' 'unsafe-inline';

Unfortunately, if the javascript error has disappeared, it still doesn't work.

Do you have an example working on your configuration to access to X and YS var ? Thanks :)

[willy-dauphin]

Here's a solution for getting data in Javascript via the API. But it's a complicated and asynchronous method. Any other solution? Thanks for your help.

SQL :

SELECT
    'Category A' AS x,
    10 AS y
  UNION ALL
  SELECT
    'Category B' AS x,
    20 AS y
  UNION ALL
  SELECT
    'Category C' AS x,
    15 AS y
  UNION ALL
  SELECT
    'Category D' AS x,
    25 AS y
  UNION ALL
  SELECT
    'Category E' AS x,
    30 AS y

Javascript :

async function fetchDataFromAPI() {
    try {
        const url = window.location.href;
        const queryIdMatch = url.match(/queries\/(\d+)/);
        if (!queryIdMatch) {
            console.log('Impossible de trouver l\'ID de la requête dans l\'URL');
            return null;
        }
        const queryId = queryIdMatch[1];
        
        // Faire l'appel API
        const apiUrl = `/api/queries/${queryId}/results`;        
        const response = await fetch(apiUrl);
        if (!response.ok) {
            throw new Error(`Erreur API: ${response.status}`);
        }
        
        const data = await response.json();        
        return data.query_result?.data || data;
        
    } catch (error) {
        console.error('Erreur lors de l\'appel API:', error);
        return null;
    }
}

fetchDataFromAPI().then(function (val){
   var items = val.rows;
   var x = [];
   var y = [];

   for (var i = 0; i < items.length; i++) {
     x.push(items[i].x);
     y.push(items[i].y);
   }
   var data = [
     {
       x, y,
       type: 'bar'
     }
   ];
   Plotly.newPlot(element, data);
});

View (it works !) :

[yoshiokatsuneo]

@willy-dauphin

I think you need to set "X column" and "Y column" fields.

[willy-dauphin]

Wow... I don't even know how I missed this. I was stuck on the code when it was written in black and white on the same page. Thanks for the obvious!