How to SELECT user defined columns (strings) with Ecto?

I am wrapping my head around this for days now… how can you select dynamic columns defined by the user at runtime? There are numerous threads about similar problems. But none of these solve our problem…

We are building some kind of CMS. So tables and columns are defined by the user at runtime. To select the records from the database we basically have everything available as strings:

schema = "my_schema"
table = "my_table"
columns = ["id", "title", "content"]

We could simply write a raw query to get the data.

"""
SELECT #{Enum.join(columns, ",")}
FROM #{schema}.#{table}
"""
|>  Repo.query!()

But for multiple reasons we would like to use the query language. This would be easy, if we just cast all the strings to atoms. Unfortunately this is not an option because of the atom limit. So we need to use strings. But Ecto does not support strings in select?! You also cannot just select all columns with a custom table name.

Ecto 3.8.4 comes with the new literal(...) support in fragment. This will work but is still not dynamic:

from(r in table)
|> select([
  fragment("?", literal(^"id")),
  fragment("?", literal(^"title")),
  fragment("?", literal(^"content"))
])
|> Repo.all(prefix: schema)

I cannot use Enum.map inside select and I cannot use fragment outside select… am I missing something? This should be simple, right? :thinking:

1 Like

I doubt ecto supports that usecase currently. It very much expects the developer to be in charge of data and therefore the code to be able to use atoms not strings.

In most cases, you’d want a similarly-virtualized data storage (star-schema, for instance) for things like this - defining columns at runtime doesn’t help much if you need to run DDL to actually create them.

Maybe the atom limit is something that you can live with? What if you restricted the column names to those available in the tables? Query them once on app boot, build atoms and then convert user supplied string to existing atoms.

You can use a map instead of a list in a combination with select_merge:

query = from t in "table", select: %{}

["id", "title", "content"]
|> Enum.reduce(query, &select_merge(&2, %{^&1 => fragment("?", literal(^&1))}))
6 Likes

That’s what I tried this morning, but totally missed that literal needs to be used with a fragment

Would be interesting if maybe Ecto.Query.API.field/2 could support string columns as well as atom columns. Then there wouldn’t be the need for using literal in the first place and it would properly support bindings.

The Postgres binary protocol doesn’t support bindings for identifiers (table names, column names, collation names, …) so its not possible to avoid string interpolation at that point. Its also the reason, I assume, that Ecto interpolates them into the query string.

literal wraps the string in database-specific quotes so that injection attacks are still prevented.

All this shouldn’t really be different between field(a, ^:column) or field(a, ^"column") though. Both require the same transformation from either atom or string to putting it into the query as far as I understand the system.

Thanks! That did the trick. I already tried select_merge but I forgot to try a map with string keys there. Works perfectly for my use case.

2 Likes