I need to build a query like the following except with an arbitrary number of tables, 36 to date. Is this approachable through ecto? Do you see any limitations? I accomplished something similar with one table, but need to union and select from subqueries.
SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, * FROM (SELECT id, message_timestamp, receipt_timestamp, data, 'skope_0' AS table FROM skope_0 UNION SELECT id, message_timestamp, receipt_timestamp, data, 'skope_1' AS table FROM skope_1 UNION SELECT id, message_timestamp, receipt_timestamp, data, 'skope_2' AS table FROM skope_2 UNION SELECT id, message_timestamp, receipt_timestamp, data, 'skope_3' AS table FROM skope_3 UNION SELECT id, message_timestamp, receipt_timestamp, data, 'skope_4' AS table FROM skope_4 UNION SELECT id, message_timestamp, receipt_timestamp, data, 'skope_5' AS table FROM skope_5) as TD WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async_time')::float DESC NULLS LAST LIMIT 20;
def get_data_range(_, table_name, from, to) do
order_by = [
desc_nulls_last: dynamic([_], fragment("(data->>'async_time')::float")),
# asc_nulls_last: dynamic([_], fragment("(data->>'edt')::float"))
]
TimeseriesRepo.all(
from(d in table_name,
where: d.message_timestamp >= ^from,
where: d.message_timestamp <= ^to,
distinct: d.data["data_id"],
order_by: ^order_by,
limit: 20,
select: %{
id: d.id,
message_timestamp: d.message_timestamp,
receipt_timestamp: d.receipt_timestamp,
asynctime: d.data["async_time"],
data_id: d.data["data_id"],
url: d.data["data_url"]
}
)
)
end
