Bonsoir à tous,
C’est un problème sans être vraiment un problème, car cela marche, mais cela ressemble à un bazouka pour tuer une mouche …
Mon objectif global, pour avoir le contexte, est de créer un sensor via template pour définir l’état probable du portail automatique si je n’ai pas un état franc ( pas fermé et pas ouvert). Pour ce faire, j’ai un ensemble de conditions en fonction des derniers états validés connus (via Template jinja simple) et de la dernière séquence de commandes envoyés (sensor sql).
Ma requête sql est aussi plutôt simple: je recherche les 6 dernières commandes d’état « ON » (je ne prends pas le off , car juste le contact sec m’intéresse) pour les switchs switch.plusuniportail_switch_0 (contact no pour ouverture/fermeture piéton) et switch.plusuniportail_switch_1 (contact no pour ouverture/fermeture voiture) rien de problématique encore à ce niveau, ma requête fonctionne à merveille (je vous conseille d’ailleur DB Brower for SQlite qui est très pratique pour les tests)
La ou je trouve cela compliqué, c’est la prise de tête pour sortir des data exploitable du résultat de requête en pseudo json via le jinja… Je suis obligé de concaténé chaque colonne en espèce de json stringifier (et pas l’ensemble pour ne pas dépasser les 255 car) pour ensuite traduire cela en jinja en sachant que le attribut_A[i] va avec le attribut_B[i], etc … 
On ne pas pas faire plus simple ???
Requête sql de base :
SELECT
datetime(julianday(states.last_updated_ts , 'unixepoch')) as last_updated_ts,
CAST(round((julianday(datetime('now')) - julianday(states.last_updated_ts , 'unixepoch'))*24*60*60) AS INTEGER) as since_sec,
CASE WHEN states_meta.entity_id = 'switch.plusuniportail_switch_0' THEN 'partial' ELSE 'complet' END AS sent_command,
states_meta.entity_id
FROM
states
LEFT JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE
states.state = 'on'
AND
(
states.metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'switch.plusuniportail_switch_0'
)
OR
states.metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'switch.plusuniportail_switch_1'
)
)
ORDER BY
last_updated_ts DESC
LIMIT
6
Résultat normal, mais malheureusement inexploitable dans Home Assistant :
| last_updated_ts | since_sec | sent_command | entity_id |
|---|---|---|---|
| 2025-03-05 19:44:15 | 255041 | partial | switch.plusuniportail_switch_0 |
| 2025-03-05 18:53:22 | 258094 | complet | switch.plusuniportail_switch_1 |
| 2025-03-05 18:52:52 | 258123 | complet | switch.plusuniportail_switch_1 |
| 2025-03-05 18:52:47 | 258128 | complet | switch.plusuniportail_switch_1 |
| 2025-03-05 18:41:20 | 258815 | complet | switch.plusuniportail_switch_1 |
| 2025-03-05 18:40:45 | 258851 | complet | switch.plusuniportail_switch_1 |
Requête sql avancée pour concaténer les résultats en pseudo tableau json:
SELECT
sum (grp) as count,
'{"data":["' || group_concat(last_updated_ts,'","') || '"]}' as last_updated_ts,
'{"data":["' || group_concat(since_sec,',') || '"]}"' as since_sec,
'{"data":["' || group_concat(sent_command,'","') || '"]}' as sent_command,
'{"data":["' || group_concat(entity_id,'","') || '"]}' as entity_id
FROM
(
SELECT
1 as grp,
datetime(julianday(states.last_updated_ts , 'unixepoch')) as last_updated_ts,
CAST(round((julianday(datetime('now')) - julianday(states.last_updated_ts , 'unixepoch'))*24*60*60) AS INTEGER) as since_sec,
CASE WHEN states_meta.entity_id = 'switch.plusuniportail_switch_0' THEN 'partial' ELSE 'complet' END AS sent_command,
states_meta.entity_id,
FROM
states
LEFT JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE
states.state = 'on'
AND
(
states.metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'switch.plusuniportail_switch_0'
)
OR
states.metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'switch.plusuniportail_switch_1'
)
)
ORDER BY
last_updated_ts DESC
LIMIT
6
)
SQL avancée résulta :
| count | last_updated_ts | since_sec | sent_command | entity_id |
|---|---|---|---|---|
| 6 | {« data »:[« 2025-03-05 19:44:15 »,…]} | {« data »:[253799,…]} | {« data »:[« partial »,« complet »,…]} | {« data »:[« switch.plusuniportail_switch_0 »,…]} |
Soit mon capteur sql sensor :
sql:
- name: portail_ShellyCmd_SQL_history
query: >
(la requête vient ici , je vous en fait grace ;) )
column: count
et finalement le template jinja pour accéder au données (l’une des données, la 3ieme entrée pour l’exemple)
{{ (state_attr('sensor.portail_shellycmd_sql_history', 'last_updated_ts') | from_json ).data[2]}}
{{ (state_attr('sensor.portail_shellycmd_sql_history', 'since_sec') | from_json ).data[2]}}
{{ (state_attr('sensor.portail_shellycmd_sql_history', 'sent_command') | from_json ).data[2]}}
{{ (state_attr('sensor.portail_shellycmd_sql_history', 'entity_id') | from_json ).data[2]}}
Je suis preneur de toute critique et optimisation !
Par avance merci
