Handling SQL result set

Dashboard
1

Hi,

I'm trying to understand how node red is handling SQL resultsets and how to visualize them as a graph.

My SQL result return values like this and I would like to convert it into a graph on my dashboard. I guess I need a crash course in how to handle this data..

Can someone please help?

SQL%20result
graph

Kind regards

O

2

If you look at the info panel for the dashboard it has a link to this page which explains what format the data needs to be in:
https://github.com/node-red/node-red-dashboard/blob/master/Charts.md

If you are just starting out reading one of the tutorials online about javascript arrays and objects is time well spent. The w3schools one is often recommended.
This page is also useful https://nodered.org/docs/user-guide/messages

3

You could use a function node to loop through the array and node.send({"topic" : msg.payload[i].EventType, "payload" : msg.payload[i].TotalTime});

In essence, turn each array element into a single message with a topic and payload.

Edit...
I must admit I haven't tried this with a bar graph but it works for line graph

4

it does work :smiley:

image
image.png1028Ă—143 13 KB

image

flow....

[{"id":"e57ae4fb.942b78","type":"inject","z":"68a418da.615bf8","name":"Fake DB Result","topic":"","payload":"[     {         \"EventType\": \"Hei\",         \"TotalTime\": 608     },     {         \"EventType\": \"Production\",         \"TotalTime\": 25405     },     {         \"EventType\": \"Serilizering\",         \"TotalTime\": 6090     } ]   ","payloadType":"json","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":140,"y":80,"wires":[["4f4ff624.150a88"]]},{"id":"4f4ff624.150a88","type":"function","z":"68a418da.615bf8","name":"loop through data and send to chart","func":"for(let i = 0 ; i < msg.payload.length; i++){\n    node.send({\"topic\": msg.payload[i].EventType, \"payload\" : msg.payload[i].TotalTime })\n}","outputs":1,"noerr":0,"x":400,"y":80,"wires":[["374cb33b.bc9d1c"]]},{"id":"374cb33b.bc9d1c","type":"ui_chart","z":"68a418da.615bf8","name":"","group":"1a45cdd4.2db222","order":1,"width":0,"height":0,"label":"chart","chartType":"bar","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":630,"y":80,"wires":[[]]},{"id":"1a45cdd4.2db222","type":"ui_group","z":"","name":"Default","tab":"35ff53eb.0e3c8c","disp":true,"width":"6","collapse":false},{"id":"35ff53eb.0e3c8c","type":"ui_tab","z":"","name":"Menu","icon":"fa-tachometer","order":1,"disabled":false,"hidden":false}]
5

Although the above solution works, i think it's really not the "node-red way" to solve it. Why should one use a function node with "node.send()" for such a trivial problem, where a simple split node is sufficient!?

Screenshot%20at%202019-04-22%2019-28-33

Edit: change node is missing here - see below

6

Thank you moebius,

I really like (and support) your idea of doing things simple, but your code does not seems to work.

Kind regards

O

7

hi, I don't normally use the split node as I figured it would have less control and more overhead than say using a function node and a map() or for loop but i am always willing to learn & intrigued enough to see how the split node works.

i imported your flow but unfortunately it doesnt work?

Could you re-import the flow you have posted and check something didnt get lost in the copy/paste to forum?

cheers.

8

Sorry, my solution was incomplete...here you go

Screenshot%20at%202019-04-22%2021-49-11
Screenshot at 2019-04-22 21-49-11.png828Ă—101 8.35 KB 

[{"id":"8144efff.0ea0e","type":"inject","z":"c421dfeb.3e752","name":"Fake DB Result","topic":"","payload":"[     {         \"EventType\": \"Hei\",         \"TotalTime\": 608     },     {         \"EventType\": \"Production\",         \"TotalTime\": 25405     },     {         \"EventType\": \"Serilizering\",         \"TotalTime\": 6090     } ]   ","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":537,"y":2762,"wires":[["13ab48eb.9794b7"]]},{"id":"e7ba3454.cda458","type":"ui_chart","z":"c421dfeb.3e752","name":"","group":"7d105c7b.563384","order":1,"width":0,"height":0,"label":"chart","chartType":"bar","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1136,"y":2755,"wires":[[],[]]},{"id":"13ab48eb.9794b7","type":"split","z":"c421dfeb.3e752","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":734.5,"y":2760,"wires":[["c7d8ea63.896f38"]]},{"id":"c7d8ea63.896f38","type":"change","z":"c421dfeb.3e752","name":"","rules":[{"t":"move","p":"payload.EventType","pt":"msg","to":"topic","tot":"msg"},{"t":"move","p":"payload.TotalTime","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":923,"y":2758,"wires":[["e7ba3454.cda458"]]},{"id":"7d105c7b.563384","type":"ui_group","z":"","name":"Data new","tab":"631762ac.ba822c","order":1,"disp":false,"width":"10"},{"id":"631762ac.ba822c","type":"ui_tab","z":"","name":"Greenhouse","icon":"dashboard","order":13}]
9

Yes, that is working and with good readable code.:slight_smile:

Thank you.

10

@olab you changed the solution to one that works the same but with more nodes and is likely less efficient? I'm hurt :wink:

@moebius thanks for showing me. However, with the overhead of 2 nodes (vs 1) and all the code behind them 2 nodes, your solution may be more "node-red" however I would argue my solution is actually more readable, more concise and likely faster and as such I'll continue to do it my way but I appreciate you showing me the working example.

As is often the case, there are more than one way to skin a cat.

11

Steve,
the function node actually runs inside a sandbox - so there is always the overhead of going into and out of that. As it is cached it is usually very small so is insignificant in most situations, (<1mS). Whether it is faster than two nodes without a sandbox would need to be tested but is unlikely :slight_smile:

Both ways are "Node-RED" ways - one because it uses the built in nodes, and gets the job done... the other because it encourages users to learn and explore javascript in order to do more advanced things. Both are good outcomes.

1 Like
12

@Steve-Mcl
If you follow your idea consistently, you would also code the sql query into the function node and also the output. Than you would have a flow with only one function node, which maybe contains more than 100 lines of code - this is more readable?

And how many mistakes could you make in all those lines? And if 10 people should code the same, it would be 10 times different, maybe one with bugs, one faster / slower and so on.

The predefined nodes allow one to code fast and readable for everyone. Also, i would assume they have lesser bugs and better speed than my own implementation.

Did you even test the "speed" of your function node? And even if the function could do it 50ms faster, the user just wouldnt notice...it draws a chart in a browser!

If you like to recode already existing functionality again and again in function nodes, with more bugs, less speed and more effort to read and maintain, it's your decision.

I only show another possible way, to anyone who's interested...

13

Hey moebius, in case my humour was a little vague (or crap as usual) there was a fair amount of tongue in cheek intended. On to my reply...

Now thats taking it to the extreme - of course not & hopefully others are not taking what I said in that way. In this instance I used 3 lines of pure JS code in 1 node.

Perfect reasoning for choosing the built in nodes over the solution I provided. Something I will take on board.

I did not & yes its academic.

I am hardly advocating that approach but your point is valid (except I dont agree on the "more effort to read and maintain" part in this particularly benign instance. Each to their own).

As I said, always more than one way to skin a cat.

Don't get me wrong moebuis, I do 100% agree with using the nodes as a first choice and where it makes sense but sometimes you end up with complex spaghetti, subflows and several layers of change/switch nodes that can be even more difficult to grasp & maintain.

14

We provide you the toolbox - it has fixed sized sockets and an adjustable spanner. Both can be used to help build things.

1 Like
15

@Steve-Mcl Taking something to the extreme in you imagination helps sometimes - and if you look at some threads in the forum, people already do it for real :slight_smile:
I'm glad you could see my points!

@dceejay
I think the use of the function node or dedicated nodes is not equal.
Especially for a beginner the function node is a lot more difficult to handle and error-prone. And in this case, where op asks for a way to put the array data into the chart node, the solutions are not even.

But hey, with the function node he can learn how debugging was in the good old days, where instead of breakpoints and inspection tools, the only way was to print out something during runtime... :wink: