Ecto query: define the field for where clause by a variable

Condensed question

I want to craft a query in which the field a where clause is examining comes from a variable. Naively I would write that like

def query(field_id) do
  from e in SomeModel, where: not is_nil(Map.get(e, ^field_id))
  ...

This does not work because I cannot call arbitrary function inside a query. I tried also to solve this with fragment but in the end I have to acknowledge that Ecto is right to prevent me from sneaking in strings to an SQL statement.

Background

The background of the question is a polymorphic belongs_to association. I am designing a multilingual database. It has several tables, each entry of the table is translatable.

So I have the relations

SchemaA has_many Translations
SchemaB has_many Translations

Translations belongs_to SchemaA
Translations belongs_to SchemaB

Now lets say I want to query a SchemaA entry in a certain language

def query(search_string, language) do
  from tl in Translations, where: like(tl.content, ^search_string) and tl.language == language
    and not is_nil(tl.schema_a_id),
  order_by: tl.content,
  ...
end

It turns out that I will have quite a lot of SchemaA like models, so I would have to write basically the same query function a lot of times. Unless I find a way to define the is_nil(tl.schema_(a|b|c|d...) part with some kind of variable.

Alternatives, that I am considering

  • One translations table for each Schama(A|B…), then I could pass the Schema struct as argument to the query function. Disadvantage: common functionality for each Translatable item (like add translation to this item) harder to implement.
  • many_to_many relations between Translations and and each Schema(A|B|C|…). Maybe the way to go. However, I am a bit reluctant due to all the junction tables that I would need.

The simplest way would be to manipulate the SQL statement, but Ecto does not allow me to do that (something I actually agree with).

Any other ideas?

Thanks for your time

Does field(e, ^field_id) work?
https://hexdocs.pm/ecto/Ecto.Query.API.html#field/2

2 Likes

Wow. How could I have overlooked this. Thanks a lot.