The current SQL integration can only process one result field at a time. It would be nice if the integration could handle multiple result fields. This could significantly reduce the number of queries and would make maintenance of the SQL integration clearer and simpler.
Use case
I am using MySQL as db for Home Assistant. With Stored Procedures and Functions I calculate all kinds of things from HA’s data and then make intensive use of the SQL integration to show that.
A simple example is a table I keep with energy prices. Amongst other purposes, this table is used to provide current prices for the energy dashboard. At the moment this requires six queries (2x Consumption, 2x Production, Gas, Water). If the SQL integration could handle multiple result fields, it would be just one query.
I read that yaml support for HA is coming back. Yay! In yaml, it could look like this:
Current
sql:
- name: Price Consumption Tariff 1
query: SELECT * FROM ...
column: "cons1"
- name: Price Consumption Tariff 2
query: SELECT * FROM ...
column: "cons2"
...
resulting in sensors:
sensor.price_consumption_tariff_1
sensor.price_consumption_tariff_2
...
Proposal
sql:
- name: Price
query: SELECT * FROM ...
columns:
- column: "cons1"
name: "Consumption Tariff 1"
- column: "cons2"
name: "Consumption Tariff 2"
...
resulting in the same sensors:
sensor.price_consumption_tariff_1
sensor.price_consumption_tariff_2
...
I have no idea how much the SQL integration is used, but of my 70 queries (more planned) there would be 5, 6 or so left. With ten times fewer queries and a lot less data over the line, I think HA should benefit from that.