How to build a query with dynamically generated columns?

Hi, all. I’m building an analytics system. In this system, tables are created by the user-provided metadata on the fly, so there’s no way to know the column names in compile time. Even though, I still want the composability of Ecto query. The problem is, I can’t find a proper way to work with absolutely dynamic column names.

For example, when I want to add a where clause to some query, I can’t write

where(query, [q], field(q, ^column_name) > 100)

because the column_name is a string, not an atom.

But it should not be an atom since it was generated by user-specified metadata and thus has infinitely many possibilities. If I use String.to_atom/1 to convert the strings to atoms, the atom table can blow up over time.

By the way, fragment(^column_name) and fragment("#{column_name}") do not work either because fragment does not allow the first argument to be pinned or be a string with interpolation.

Is there a way to do such a job in Ecto, or should I use another library to achieve my goal?


I think that dynamic fragments might be the right tool in this case. I have no practical experience to share, however I’m sure there are others here that do!

Thank you for your quick reply. I’ll try it, but my instinct tells me that it won’t work because all the examples hard code the field names in the query.

It does not work, just as I expected :sweat_smile: