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_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
