I need help to find closest match from DB

First result of google search gives

Not sure exactly what your db array looks like, i may of missed it.

You where close with your reduce method. The prev and curr are probably objects so you would do something more like below. I have create a array that i think your db request should be able to return. goal can be set in msg.topic, in the inject node.

[{"id":"dc450f89.40902","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","v":"26","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"hue\":10,\"value\":6},{\"hue\":20,\"value\":6.5},{\"hue\":30,\"value\":7},{\"hue\":30,\"value\":7.5},{\"hue\":40,\"value\":8},{\"hue\":50,\"value\":8.5},{\"hue\":60,\"value\":9},{\"hue\":80,\"value\":9.5},{\"hue\":80,\"value\":10}]","payloadType":"json","x":230,"y":2080,"wires":[["be043b0c.8a3898"]]},{"id":"be043b0c.8a3898","type":"function","z":"8d22ae29.7df6d","name":"","func":"let goal = msg.topic;\nmsg.payload = msg.payload.reduce((prev, curr) => Math.abs(prev.hue - goal) > Math.abs(curr.hue - goal) ? curr : prev )\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":2080,"wires":[["dcd8ea98.f4e16"]]},{"id":"dcd8ea98.f4e16","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":570,"y":2080,"wires":[]}]

also your counts= [msg.payload.db] is adding an outer array no need for [ ]

I don't think @ajbuk21 actually wants the nearest number, he wants the index of the nearest number so that he can use it to get another attribute of the object at that position in the array. I don't think that reduce will do that, but I haven't investigated it thoroughly.

The OP wants to look up the PH with the closest Hue. If he returns the hue along with with the PH from original DB request , he can then just search for closest hue and he will have the PH. Or he can retrun the closest hue only and then do another DB request with the hue to return the PH. The first approach would be simpler and only require one DB request. The reduce method will work.

Edit/ I think it would be possible(depending on DB) to do the look up for closest in the DB request also

Yes, that approach would work, but as I suggested, it would be more efficient to query both the hue and the ph together initially, then find the index with the nearest hue and then the ph is immediately available without another db query.
Also a binary search of the (already sorted) array, or even a sequential search stopping when the nearest is found, is going to be more efficient than testing every element in the array as reduce() does.

Thats what i suggest when i say "return hue along with PH from original DB request"

But better, just to do the whole thing in db if possible.

Agreed, I didn't realise you could do that.


thank you Colin and E1cid you got me there in the end BIG THANKS

it would of been nice to get the DB to do the work, i did just try but i got was syntax errors??


Unless you show us the query you used and the error then it is impossible to comment.

SELECT ABS(40 - hue) as nearest, value
FROM ph_results
ORDER BY nearest ASC

where 40 is the search value


This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.