Good to hear that the query command works at your side.
Did you update your container to a recent HA version?
Regarding your automation, i also think the template is incorrect.
Maybe you can try one thing, if that does not work, i will try to replicate your query and automation to debug the problem.
Since result is a collection, in order to be able to return a recordset (multiple records), depending on the query, you’ll have to specify the item number.
in your case:
prueba = {‘result’: [{‘idAP’: 379}]}
you can see the square brackets around the value, because it is a collection.
I was wondering if I can seek your advice. I am able to do a test mysql_query in dev tools.
But wondering how to call your service and display the mysql response in lovelace UI? (2nd screenshot shows entity display with desired mysql response as secondary info.)
Happy to use a custom UI component if that is required.
Thank you in advance for your help. Happy holidays!
I’m not sure, but afaik, you’ll always have to respond to a trigger in order to call any HA service.
You could create a Template Helper and use that in Lovelace.
But you’ll still have to call the service now and then to update the Template Helper’s underneath value.
The trigger to call the service could be a timer or other event such as an entity’s state that is changing.
I’m not sure if this approach makes sense for you.
Maybe if you could explain your use case in more detail, hopefully someone else might come up with an other approach.
My use case: As you can see from my lovelace screenshot above, I display the last_change timestamp for each of my entities. However the last_change timestamp does not always reflect the last change time if I reboot HA, last change timestamp would then be the reboot timestamp. (This is a well-known gripe for users Persist last_changed between HA restarts)
The idea is to do a mysql select like shown in my first screenshot to retrieve the correct last_change value for each entity and display with each entity in lovelace.
As said, you’ll have to call the MySQL service from a trigger. In your case the trigger will be HomeAssistant starting up.
So you need to follow these steps:
First create a Helper entity of type Date and time and give it a name, for example Door1TimeStampHelper
Next create an Automation that populates the Helper entity when HA starts:
Finally you should now be able to use Door1TimeStampHelper entity in Lovelace
I haven’t tested above literally, so you may have to adjust the entity names and the SQL query. But that should be no problem, since I saw you already had an advanced query in your screenshot
You will actually also have to update the Helper entity at the moment the garagedoor is being opened or closed.
For that you can follow one of below scenarios:
Add an extra trigger to the automation above, to also fire when the state of the garagadoor changes.
Create a separate automation to set the Helper’s entity value to the state of the garagedoor when that changes
@Yes Hmmm, I was trying to avoid having to create more entities like input_datetime.Door1TimeStampHelper to store timestamp values that are already in DB.
Is there no way to display the results of a mysql select for each entity in lovelace? (where the user does not have to tap/click anything)
I was browsing through the list of available official HA integrations when
I came across the SQL integration. Could this be what you’re looking for?
I haven’t tried it myself (yet), but it seems it lets one create sensors based on SQL Queries. Not just for MySQL databases but for other DB engines as well.
With sensors you should be able to display the retrieved values in Lovelace without a user having to press a button etc.
You will still need a query per entity I guess.
Forum thread that discusses how to handle query updates.