Dear friends,
I would like to ask what function to use to compare and sort several values read from mysql.
In my case, an array of objects is returned to me and each object contains data from meters.
In order to continue to work correctly with data, I need to sort it by id_meter in the object.
I tried the Switch node, but it didn't work as I imagined.
Compare id_meter and, in case of a match, output only the data for this object.
Would anyone please be willing to advise me and guide me?
Thanks
Can you provide the flow?
what did you compare in the switch node?
This is my Switch node
[{"id":"f1eed41f.423e48","type":"inject","z":"bf25ef93.b69ed","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":120,"wires":[["dc8dd24f.ae69d"]]},{"id":"dc8dd24f.ae69d","type":"function","z":"bf25ef93.b69ed","name":"Read Elm - Previous month","func":"msg.topic = \"select id_meter,high_tarif,low_tarif from table_value where (time_stamp between '2020-12-31 22:00:00' and '2020-12-31 23:59:59') ORDER BY `time_stamp` DESC\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":340,"y":200,"wires":[["dc58c944.cecd38"]]},{"id":"dc58c944.cecd38","type":"mysql","z":"bf25ef93.b69ed","mydb":"d8e4b790.e44c08","name":"","x":550,"y":200,"wires":[["6c47a45.47cad5c","3d7237ef.d0ff98"]]},{"id":"6c47a45.47cad5c","type":"debug","z":"bf25ef93.b69ed","name":"","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":640,"y":80,"wires":[]},{"id":"3d7237ef.d0ff98","type":"switch","z":"bf25ef93.b69ed","name":"","property":"payload.id_meter","propertyType":"msg","rules":[{"t":"eq","v":"\"05060946\"","vt":"msg"}],"checkall":"true","repair":false,"outputs":1,"x":550,"y":280,"wires":[["44e5a2aa.5e29bc"]]},{"id":"44e5a2aa.5e29bc","type":"debug","z":"bf25ef93.b69ed","name":"","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":560,"y":340,"wires":[]},{"id":"d8e4b790.e44c08","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"ELM","tz":"","charset":"UTF8"}]
In your switch node you are comparing against msg."05060946"
change 'msg' to string 'a/z' and see how it goes
If you just need to sort the array then you should do that in the database query, it can sort on multiple columns for you. Make sure the id field is indexed. However if what you want to do is to send them down different message paths then first you need to use a Split node which will split the array into individual messages then you can use a Switch node to send them down the different paths.
It may be that isn't what you really want to do though, tell us what you want to achieve with the data and someone will suggest the route to take.
I tried this variant before I entered a question here.
I've read about it in other user queries.
But this method also does not work for her.
Dear Colin,
I am trying to process an "application" in which the values of measured data from meters will be displayed.
Data from individual meters are stored in a database and then I want to display individual values in graphs and tables like many good people here.
I have read many posts here for discussion, some of my steps have already succeeded.
I want to read the values of meters from the database, assign them correctly and then display them further in a table or graph in the dashboard.
In that case I would query the meters separately I think. Then you will know that each message contains all the data for that meter. Easier to do that than split it up later.
Thank you for the inspirational idea.
At the same time, I will ask if it is possible to enter several sql queries in one function node and then divide them into flow or global value?
Thank you
The node red doc Writing Functions shows you how to send multiple messages one after the other. Something like
let messages = [
{topic: "select ..."},
{topic: "select ..."},
{topic: "select ..."}
]
return [messages]
Normally one would not put the results in a context variable but would pass the messages along to wherever they have to get.
You mentioned chart and tables, so to do that I would send all the results through the nodes to structure the data appropriately for the charts and then right at the end use a Switch node to split them up and send them to the appropriate charts.
It might be a good idea to add a property to the message going to the SQL node which identifies the meter. That should get passed down the wires unaffected by intermediate nodes and then makes it easy to split them at the end. So maybe you want something like (untested)
let messages = [
{topic: "select ... where meterId='012605' ", id_meter = '012605`},
{topic: "select ..where meterId='012606' ", id_meter = '012606`},
{topic: "select ..." etc}
]
return [messages]
But then you can do that more cleanly with (untested)
const ids = ["02489", "034786","780365"]
let messages = []
for (let id =0; id <= ids.length; id++) {
ids.push( {topic: `select ... where id_meter = "${ids[i]}", id_meter = ids[i]})
}
return [messages]
Ahhh, I missed that this was one big array. If you fed the output of the mysql node thru a split node, it will send each row along as a seperate msg. Then you should be able to use the switch node to process the data.
Thanks Colin and Zenofmund for the help.
I have successfully reached some path to the goal.
I believe it can be simplified, but it will be for me to continue my studies.
Thanks
[{"id":"bf25ef93.b69ed","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"31747531.95f4aa","type":"inject","z":"bf25ef93.b69ed","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":80,"wires":[["55d0cf0.9e2263"]]},{"id":"4f5cf810.522d58","type":"mysql","z":"bf25ef93.b69ed","mydb":"d8e4b790.e44c08","name":"","x":410,"y":160,"wires":[["9dbdc4ce.234768"]]},{"id":"55d0cf0.9e2263","type":"function","z":"bf25ef93.b69ed","name":"Read Elm - Previous month","func":"let messages = [ \n {topic: \"SELECT * FROM table_value WHERE id_meter = '\"+flow.get(\"Elm4_id\")+\"' AND time_stamp BETWEEN '2021-01-22 23:00:00' AND '2021-01-22 23:59:59'\"}, \n {topic: \"SELECT * FROM table_value WHERE id_meter = '\"+flow.get(\"Elm5_id\")+\"' AND time_stamp BETWEEN '2021-01-22 23:00:00' AND '2021-01-22 23:59:59'\"},\n]\nreturn [messages]","outputs":1,"noerr":0,"initialize":"","finalize":"","x":200,"y":160,"wires":[["4f5cf810.522d58"]]},{"id":"9dbdc4ce.234768","type":"json","z":"bf25ef93.b69ed","name":"","property":"payload","action":"str","pretty":false,"x":550,"y":160,"wires":[["206f3cf6.32bc4c"]]},{"id":"206f3cf6.32bc4c","type":"function","z":"bf25ef93.b69ed","name":"First function","func":"msg1 = { payload: JSON.parse(msg.payload) };\nreturn msg1;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":590,"y":220,"wires":[["5b6def48.376fc8"]]},{"id":"5b6def48.376fc8","type":"switch","z":"bf25ef93.b69ed","name":"","property":"payload[0].id_meter","propertyType":"msg","rules":[{"t":"cont","v":"05060948","vt":"num"},{"t":"cont","v":"14787607","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":730,"y":220,"wires":[["b547ec05.055658"],["457557f7.22eae"]]},{"id":"b547ec05.055658","type":"function","z":"bf25ef93.b69ed","name":"2-Function","func":"var x1 = msg.payload[0].high_tarif\nvar x2 = msg.payload[0].low_tarif\n\nflow.set(\"Elm4_pm_ht\", x1)\nflow.set(\"Elm4_pm_lt\", x2)\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":890,"y":180,"wires":[[]]},{"id":"457557f7.22eae","type":"function","z":"bf25ef93.b69ed","name":"3-Function","func":"var x1 = msg.payload[0].high_tarif\nvar x2 = msg.payload[0].low_tarif\n\nflow.set(\"Elm5_pm_ht\", x1)\nflow.set(\"Elm5_pm_lt\", x2)\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":890,"y":240,"wires":[[]]},{"id":"d8e4b790.e44c08","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"ELM","tz":"","charset":"UTF8"}]
1 Like
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
