I’m a bit stuck on a SQL sensor I’m trying to create; the database seems to be missing data in some columns that I need. I know I could do this with a load of YAML attribute compares, but it seemed cleaner to do it with one database query… at least until I tried
The requirement is to know who came home most recently - i.e., which of several device_tracker entities has the highest last_changed value.
I thought this would be a simple query on the states table, but apparently I’m not supposed to do that and have to go via states_meta. Okay then. When I do that, I get rows of data back but the vital column on states, the entity_id, is empty! The only way I’ve come up with to get it, is to join states_meta to states, but that’s a hideously slow query and probably not good for the database.
This was my original query:
SELECT entity_id
FROM states
WHERE metadata_id IN (
SELECT metadata_id
FROM states_meta
WHERE entity_id in ('device_tracker.jeffs_phone', 'device_tracker.aaa_phone',
'device_tracker.bbb_phone')
)
ORDER BY last_changed_ts DESC
LIMIT 1;
Checking states directly, all entity_ids are null:
sqlite> select count(entity_id) from states where entity_id is not null;
0
Can anyone with more knowledge of the HA schema suggest improvements to my SQL to get back the entity_id of the device_tracker with the most recent last_changed_ts value, without a big join?
One drawback of relying on last_changed is that it updates on restart or reload… this would likely cause a SQL sensor approach to get complicated as well since your query would need to filter out updates to last_changed where the previous state was “unavailable”.
Another option would be a trigger-based template sensor:
template:
- trigger:
- platform: state
entity_id:
- device_tracker.1
- device_tracker.2
- device_tracker.3
- device_tracker.4
- person.1
not_from:
- unavailable
- unknown
to: home
sensor:
- name: Most recent Homie
state: "{{ trigger.entity_id }}"
That’s pretty close to where I settled; catch anyone arriving home and stick their name into a custom text sensor. Now I just need to get rid of the irritating habit Chromecast has of losing the first few seconds of any TTS speech if it’s not been used for a while!