Suggestion about tracking orders in SQL

[demokedes]

Dear all,
I need some suggestion on how to get notified about completed orders from crypto exchange.
There is a node that is giving me a parameters about completed orders. I am getting it in the following format:

I have an idea that for each of this object to create order details (along with orderId) in the SQL database.
I am thinking of then checking on every minute/hour and check for each object whether there is an orderid already in my database. If it is do nothing, if it is not, I will create a record.
How to achieve that? Should I implement this logic in node-red or on SQL side?

Thank you for your suggestions.

Rok

[TotallyInformation]

In general, it is typically a good idea to filter out unnecessary processing as early as possible.

So probably easier to do this in Node-RED rather than SQL.

[demokedes]

Ok, understand. Which command should I use? What kind of query to check whether orderId is already in the particular column in particular table. I would need boolean.
Regards,
Rok

[dynamicdave]

I've got a set of 'tasmota' nodes around the house that report various parameters. I do a MySQL query INSERT INTO with ON DUPLICATE KEY UPDATE (using a Template node) to either write data from a new node or update data from an existing node. You can probably adapt this to suit your requirement.

INSERT INTO energy_latest_readings (node_ref, voltage, current, power) 
VALUES ( 'tasmota', {{payload.ENERGY.Voltage}}, {{payload.ENERGY.Current}}, {{payload.ENERGY.ApparentPower}} )

ON DUPLICATE KEY UPDATE
node_ref='tasmota',
voltage={{payload.ENERGY.Voltage}},
current={{payload.ENERGY.Current}},
power={{payload.ENERGY.ApparentPower}}

node_ref is a unique key in the database schema.

[TotallyInformation]

@demokedes

Ok, understand. Which command should I use? What kind of query to check whether orderId is already in the particular column in particular table. I would need boolean.

Depends on how many orderid's you need to track and how often you get a new one. If the number is manageable (say a few hundred, maybe a few thousand), then you could keep the orderid's in a global variable. Then you could use a switch node to see if the new id is in the list. When Node-RED starts, simply have a short flow that triggers on start and does a simple SQL query to get the list of orderid's into a global variable.

If the numbers are really high though (lets say 10's of thousands), I would switch to doing it in SQL because you are less likely to get memory issues.

[demokedes]

Hey,
Thank you for the suggestion. In my case it will be cca 200 orders per year, so it makes sense to go with global variable. Since till now I was working with one value per variable, can you just guide me on how to store list of values into one variable.
Thank you in advance!
Rok

[Colin]

Store them in an array in the global variable.

[ghayne]

One method:

[{"id":"3e32b9a35b640723","type":"inject","z":"8a098f827b12daf6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":220,"wires":[["9fc45dc141a86816"]]},{"id":"9fc45dc141a86816","type":"change","z":"8a098f827b12daf6","name":"","rules":[{"t":"set","p":"array","pt":"global","to":"$append($globalContext(\"array\"), [payload])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":220,"wires":[[]]}]

[demokedes]

ghayne,
Thank you very much.

1. With your method I managed to store values int o the global array. So I have currently 18 values in the global array.
2. Now I create new array of values. For this values I would like to check, whether particular is already in the list or not. If the orderIf is already in the list, I will ignore it, otherwise will have Insert command to write it into the database and also send a message so that I am aware of the order.
3. For that purpose I used array-loop function to go through each object in the array.
   What I don't know is how to check for particula orderId to check whether it is in the global variable list. How can I achieve that?
   Thank you in advance,
   Rok
   
   

[demokedes]

I achieved the same even easier with split function...where I am sending different messages for each order. But I have the same unclearity...how to check whether orderId is in the global array or not.
Thank you for your answer.
ROk

[dynamicdave]

Have a look at this link if you just want to find if a value (number ?) is in an array.

5 ways to check if an array contains a value in JavaScript

[ghayne]

@demokedes

how to check whether orderId is in the global array or not.

Getting deep into JSONata now
In this flow an inject node generates random numbers fro 0 to 9.
The change node checks if the number is already in the array and appends it if not.
After a high enough number of injects, the array is at it's maximum of 10 elements (0 -9), and
will not grow at all.

[{"id":"8a098f827b12daf6","type":"tab","label":"Flow 9","disabled":false,"info":"","env":[]},{"id":"3e32b9a35b640723","type":"inject","z":"8a098f827b12daf6","name":"Inject Random number 0-9","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"$floor($random()*10)\t","payloadType":"jsonata","x":170,"y":140,"wires":[["9fc45dc141a86816"]]},{"id":"9fc45dc141a86816","type":"change","z":"8a098f827b12daf6","name":"","rules":[{"t":"set","p":"array","pt":"global","to":"(payload in $globalContext(\"array\")) ? $globalContext(\"array\"): $append($globalContext(\"array\"), [payload])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":140,"wires":[[]],"info":"Check"},{"id":"7f35d69c6159dba7","type":"comment","z":"8a098f827b12daf6","name":"CHeck if number is in array. If not add it.","info":"CHeck if number is in array","x":510,"y":80,"wires":[]}]

@dynamicdave's methods in JavaScript might be easier to implement for you!

[E1cid]

You could simplify the JSONata to

$append($flowContext("array")[$ != $$.payload], [$$.payload])

this wiill return the context array minus the incoming payload, then append the new payload to the end of the array, this also works well for arrays of objects.

this thread has some good examples of searching and appending to arrays with JSONata

UI with selectable AIS ship target list

Hello, I am trying to create a Dashboard, where I can pick a ship/target from a drop-down list and display it's data... So far I set up a tcp:port where I receive AIS-NMEA data constantly. The "ais"-node transforms the data into readable json format (Objects?). The AIS data is sent every few seconds from several vessels (each has a specific time slot). If I want to select the data from just one special ship, l need to filter the incoming data, e.g. by ships name (or "mmsi")... ... additionall…

[ghayne]

@E1cid

$append($flowContext("array")[$ != $.payload], [$.payload])

Doesn't work in the above flow I posted!

[E1cid]

that is because the other is global i tested in flow as i dont like to mess with my global context. Change flow to global to get it to work in you above flow.

[ghayne]

I missed that, thanks!

[demokedes]

Dear @ghayne and @E1cid, I was off for few days/weeks, now I am back. With your help I managed to achieve to store orderId into the global array.
I have two questions:

1. Global array will stay permanent in the ram of nodered or it will be cleared after restart? If yes, that means, that I will have to add additional command that will copy all the orderIds from the database into the global array.

2. Now with this switch node I am checking whether there is orderId in the global array. If not it is written into the global array. But I would also like to do additional task (flow). If there is a new OrderId I would like to create insert into the database and also message through chatbot. I know how to achieve these two commands, but I am not sure, how to force them? Can I get any output/trigger from this switch flow?

(payload.orderId in $globalContext("array4")) ? $globalContext("array4"): $append($globalContext("array4"), [payload.orderId])

[zenofmud]

You may find this helpful: A guide to understanding 'Persistent Context'

[demokedes]

Hey @zenofmud ,
Thank you for answering on question 1. It is clear.
But what about question no 2.
I was just trying on my own. I was thinking to use switch node. I have array4 in which I have all the orderids. With switch mode, I would like to direct output 1 in case payload.orderId is having orderid that is already in array 4. I used bellow expression, but it is not working. Any idea what I am doing wrong?

[zenofmud]

If msg.payload.ordered is a single order then the comparison with an array is going to get a not equal result. You might want to use a function to search the global for msg.payload.ordered.