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.
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
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_manyrelations 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