SQL question

Configuration
1

Hi Folks,

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 :rofl:

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?

Thanks!

2

How about a trigger on the home zone

    trigger:
    - platform: state
      entity_id:
        - zone.home

and then compare the people that were there before and after

{% set persons_old = trigger.from_state.attributes.persons %} 
{% set persons_new = trigger.to_state.attributes.persons %} 
{% set arrived = persons_new | reject('in',persons_old)|list  %}
{% set left = persons_old | reject('in',persons_new)|list  %}
3

Can probably be done nicer (not a guru in jinja)

{{ expand(states.device_tracker) | sort(attribute='last_changed') | map(attribute='entity_id') | list | last }}
5

Ooh, Jinja sort, like that idea! I’d need to filter the list first, but that’s do-able. I’ll have a play with that!

6

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 }}"
1 Like
7

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!