** (Postgrex.Error) ERROR 42P01 (undefined_table) missing FROM-clause entry for table "s0"

Phoenix Forum Questions / Help
1

As I said in the ticket above, I have a working SQL example, blow is its conversion to ecto… any ideas?

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
2

That ticket is missing important information: what SQL is getting generated? For instance, what does TimeseriesRepo.to_sql(:all, ...) return when applied to the subquery, or to the whole query? Does the error message from Postgres show any additional detail? (or the Postgres server logs?)

1 Like
3

The query works fine till I use ORDER BY per subquery with the fragment. No additional details are shown except the query it generated which is truncated due to length…

1 Like