### Elixir version
Elixir 1.13.0
### Database and Version
14.4 (Ubuntu… 14.4-1.pgdg20.04+1), server 12.11 (Ubuntu 12.11-1.pgdg20.04+1)
### Ecto Versions
3.8.4
### Database Adapter and Versions (postgrex, myxql, etc)
postgrex
### Current behavior
Below I have provided the working SQL equivalent of what Im building in Ecto however Im given `** (Postgrex.Error) ERROR 42P01 (undefined_table) missing FROM-clause entry for table "s0"`
Thank you for your time and help!
```
def get_top(_, table_list, from, to) do
order_by = [
desc_nulls_last: dynamic([d], fragment("(?->>'async')::float", d.data)),
]
subQ = Enum.reduce(table_list, %Ecto.Query{}, fn table, acc ->
query =
from(d in table[:table_name],
distinct: d.data["data_id"],
where: d.message_timestamp >= ^from,
where: d.message_timestamp <= ^to,
order_by: ^order_by,
select: %{
id: d.id,
message_timestamp: d.message_timestamp,
receipt_timestamp: d.receipt_timestamp,
async: fragment("(data->>'async')::float"),
outer: fragment("(data->>'outer')::float"),
data_id: d.data["data_id"],
table: ^table[:table_name]
}
)
cond do
acc == %Ecto.Query{} ->
query
true ->
query |> union(^acc)
end
end)
TimeseriesRepo.all(
from(d in subquery(subQ),
order_by: [
desc_nulls_last: d.async,
],
limit: 20,
select: %{
id: d.id,
message_timestamp: d.message_timestamp,
receipt_timestamp: d.receipt_timestamp,
async: d.async,
outer: d.outer,
data_id: d.tenant_id,
table_name: d.table
}
)
)
end
```
### Expected behavior
SELECT * FROM (
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_41843_0' AS table FROM sol_41843_0 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_41843_1' AS table FROM sol_41843_1 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_41843_2' AS table FROM sol_41843_2 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_41843_3' AS table FROM sol_41843_3 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_41843_4' AS table FROM sol_41843_4 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_41843_5' AS table FROM sol_41843_5 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_72019_0' AS table FROM sol_72019_0 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_72019_1' AS table FROM sol_72019_1 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_72019_2' AS table FROM sol_72019_2 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_72019_3' AS table FROM sol_72019_3 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_72019_4' AS table FROM sol_72019_4 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_72019_5' AS table FROM sol_72019_5 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_7099_0' AS table FROM sol_7099_0 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_7099_1' AS table FROM sol_7099_1 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_7099_2' AS table FROM sol_7099_2 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_7099_3' AS table FROM sol_7099_3 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_7099_4' AS table FROM sol_7099_4 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_7099_5' AS table FROM sol_7099_5 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_727550_0' AS table FROM sol_727550_0 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_727550_1' AS table FROM sol_727550_1 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_727550_2' AS table FROM sol_727550_2 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_727550_3' AS table FROM sol_727550_3 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_727550_4' AS table FROM sol_727550_4 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_727550_5' AS table FROM sol_727550_5 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_872109_0' AS table FROM sol_872109_0 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_872109_1' AS table FROM sol_872109_1 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_872109_2' AS table FROM sol_872109_2 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_872109_3' AS table FROM sol_872109_3 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_872109_4' AS table FROM sol_872109_4 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST) UNION
(SELECT DISTINCT ON ((data#>'{"data_id"}')) id AS nu, id, message_timestamp, receipt_timestamp, data, 'sol_872109_5' AS table FROM sol_872109_5 WHERE message_timestamp BETWEEN NOW() - interval '1 day' AND NOW() ORDER BY (data#>'{"data_id"}'), (data->>'async')::float DESC NULLS LAST)
) as TD ORDER BY (data->>'async')::float DESC NULLS LAST LIMIT 20;