Description

Charts are often requested in a project management application. This brought us to integrate Chart.js natively in our framework. Please feel free to check out the Chart.js documentation in order to understand what that is for. In the next few lines we are just going to describe how UD leverages it.

Information needed in order to set up a table

Get parameters

Sometimes we need to pass to the query behind the chart some parameter using a get request.

Example: www.example.com/mytable?parentid=2302

In order to catch that get parameter you need to add an object in the parameters section. That object has tree properties:

  • type: the type (long, string, etc..) expected the parameter to take after validation
  • validation: the rules the parameter has to follow
  • name: the parameter name in the URL
"parameters": [
  { "type":"integer", "validation":"required|integer", "name":"parentid" }
]

If you want to know about the Validation check out the related page.

Query

We need to make a query to the datase in order to populate our table. The simplest thing to do is just to write the query in plain SQL and eventually connect the parameters needed from the GET section.

"query": {
  "sql": "select id, typeid, name, description FROM mytable WHERE parentid = :parentid;",
  "parameters":[
    { "type":"long", "placeholder": ":parentid", "getparameter": "parentid" }
  ]
}

As you can see the SQL parameter is inserted in the query using a placeholder: :parametername The SQL parameter is connected to the GET parameter using: “getparameter”: “parentid” We can insert as many paremeters as we need.

If you want to know more about SQL paramenters check out the Query page.

Chart Structure

The chart structure is basically taken from the structure the Chart.js library requires. Nothing is added by ugly duckling.

It is important to note that sometimes there are placeholder in the stucture. You can recognise them because they are strings starting with a #.

Here there are two placeholders but they could be more as the structure is recursively checked from UD and you are free to set all the placeholders you need.

What are the placeholder for? We need them in order to glue the data resulting from the query to the chart.

Here you can see the two placehoders:

  • #labels
  • #amounts

We will understand better how they relate to the database query in the next paragraph. For now have a look to the chart structure.

"chart": {
  "type":"bar",
  "data": {
    "labels": "#labels",
    "datasets": [{
      "label": "# of Articles",
      "data": "#amounts",
      "backgroundColor:":[
        "rgba(255, 99, 132, 0.2)",
        "rgba(54, 162, 235, 0.2)",
        "rgba(255, 206, 86, 0.2)",
        "rgba(75, 192, 192, 0.2)",
        "rgba(153, 102, 255, 0.2)",
        "rgba(255, 159, 64, 0.2)"
      ],
      "borderColor": [
        "rgba(255,99,132,1)",
        "rgba(54, 162, 235, 1)",
        "rgba(255, 206, 86, 1)",
        "rgba(75, 192, 192, 1)",
        "rgba(153, 102, 255, 1)",
        "rgba(255, 159, 64, 1)"
      ],
      "borderWidth": "1"
    }]
  },
  "options": {
    "scales": {
      "yAxes": [{
        "ticks": {
          "beginAtZero":true
        }
      }]
    }
  }
}

Chart Data Glue

We previously said that the chart structure allows the presence of placeholders in order to kwno where to put results from the query:

  • #labels
  • #amounts

The SQL query is the following:

select count(id) counted, created FROM articles GROUP BY created;

Basically this wants to count the article created per day and group them by creation date. Visualizing this data in a chart means to set a set of labels (the dates) and values (the number of articles per day).

"chartdataglue":[
  { "type":"string", "placeholder":"#labels", "sqlfield":"created" },
  { "type":"long", "placeholder":"#amounts", "sqlfield":"counted" }
]

The chartdataglue creates two json lists, the first one is created from the results coming from the query from field named created.

UD will care of sobstituing the #labels placeholder with the list created from content coming from the results of the query.

The same is happening to the #amounts placeholder and the field counted.

This meas that where in the structure there is #labels placeholder will be inserted a list containing [“31/12/2020”, “01/01/2021”, “01/01/2021”] and where there is the #amounts placeholder will be inserted a list containing [3, 2, 4].

Complete example

{
  "name": "articleschartv1",
  "metadata": { "type":"chartjs", "version": "1" },
  "allowedgroups": [ "author" ],
  "get": {
    "request": {
      "parameters": []
    },
    "query": {
      "sql":"select count(id) counted, created FROM articles GROUP BY created;",
      "parameters":[]
    },
    "chartdataglue":[
      { "type":"string", "placeholder":"#labels", "sqlfield":"created" },
      { "type":"long", "placeholder":"#amounts", "sqlfield":"counted" }
    ],
    "chart": {
      "type":"bar",
      "data": {
        "labels": "#labels",
        "datasets": [{
          "label": "# of Articles",
          "data": "#amounts",
          "backgroundColor:":[
            "rgba(255, 99, 132, 0.2)",
            "rgba(54, 162, 235, 0.2)",
            "rgba(255, 206, 86, 0.2)",
            "rgba(75, 192, 192, 0.2)",
            "rgba(153, 102, 255, 0.2)",
            "rgba(255, 159, 64, 0.2)"
          ],
          "borderColor": [
            "rgba(255,99,132,1)",
            "rgba(54, 162, 235, 1)",
            "rgba(255, 206, 86, 1)",
            "rgba(75, 192, 192, 1)",
            "rgba(153, 102, 255, 1)",
            "rgba(255, 159, 64, 1)"
          ],
          "borderWidth": "1"
        }]
      },
      "options": {
        "scales": {
          "yAxes": [{
            "ticks": {
              "beginAtZero":true
            }
          }]
        }
      }
    }
  }
}