Bottom Line: How do I show (10) or so rows, from an SQL query, on HA Dashboard ?
I have a front door lock, Kwikset 916, integrated into HA and all its events are stored in MariaDB; all this works well. I want to see the last (10) or so events related to lock/unlock. I have built the SQL query (using tools external to HA) to produce the result set I want but don’t know how to show on my dashboard. The result set is currently (2) columns, one with text, one with date. All the formatting is done in SQL. I am not sure how to get the query setup in HA as it seems the SQL integration is setup to return a single value.
I was interested in your post, and waiting to see if anyone came up with a template to do this, however yes, the SQL integration will return just one value in each created entity state. The extra query columns from the SQL will, apparently, go into attributes, but:
Note that in all cases only the first row returned will be used
Since the entity state cannot be an object in any form, this precludes returning a table or array, just a primitive value, possibly with extra ‘fields’ from just one row.
For myself, I use Node-RED, which can issue SQL queries to return and process an array (of objects) and such arrays can easily be returned in their entirety to HA in a Node-RED created Sensor entity as an attribute. Then it is a simple case of using the flex-table card, for example, based on the entity attribute.
The ‘test weekly electricity cost’ comes from a basic HA SQL integration entity (single value) the rest via Node-RED to return an array of monthly billing figures from history.
Node-RED is not as popular as Jinja templates, but I personally find it relatively easy to use for useful stuff like this. I have really only just started using SQL, but it is amazing just what you can extract from the long-term statistics data!
One node to trigger the flow every time the values change, one node to run the SQL on the database (HA long-term statistics) one node to run the analysis and build the results array, one node to send it back to HA in a sensor attribute. All these components are YMTL (Yet More To Learn) but mostly ‘out of the box’ add-ons.
