Generates a SQL fragment for accessing a translated field in an Ecto.Query.

The generated SQL fragment can be coupled with the rest of the functions and operators provided by Ecto.Query and Ecto.Query.API.

Safety

This macro will emit errors when used with untranslatable schema modules or fields. Errors are emitted during the compilation phase thus avoiding runtime errors after the queries are built.

Examples

Assuming the Article schema defined in Structured translations:

# Return all articles that have a Spanish translation
from a in Article, where: not is_nil(translated(Article, a, :es)))
#=> SELECT a0."id", a0."title", a0."body", a0."translations"
#=> FROM "articles" AS a0
#=> WHERE (NOT (NULLIF((a0."translations"->'es'),'null') IS NULL))

# Query items with a certain translated value
from a in Article, where: translated(Article, a.title, :fr) == "Elixir"
#=> SELECT a0."id", a0."title", a0."body", a0."translations"
#=> FROM "articles" AS a0
#=> WHERE (COALESCE(a0."translations"->$1->>$2, a0."title") = 'Elixir')

# Query items with a translated value in the current locale
from a in Article, where: translated(Article, a.title) == "Elixir")
#=> SELECT a0."id", a0."title", a0."body", a0."translations"
#=> FROM "articles" AS a0
#=> WHERE (translate_field(a0, $1::varchar, $2::varchar, $3::varchar, $4::varchar[]) = 'Elixir')
# Here the parameters are the following when the current locale is "en":
#  ["translations", "title", "en", ["en-001", "en"]]

# Query items using a case insensitive comparison
from a in Article, where: ilike(translated(Article, a.body, :es), "%elixir%")
#=> SELECT a0."id", a0."title", a0."body", a0."translations"
#=> FROM "articles" AS a0
#=> WHERE (COALESCE(a0."translations"->$1->>$2, a0."body") ILIKE '%elixir%')

Structured translations vs free-form translations

The Cldr.Trans.QueryBuilder works with both Structured translations and with Free-form translations.

In most situations, the queries can be performed in the same way for both cases. When querying for data translated into a certain locale we must know whether we are using structured or free-form translations.

When using structured translations, the translations are saved as an embedded schema. This means that the locale keys will be always present even if there is no translation for that locale. In the database we have a NULL value (nil in Elixir).

# If MyApp.Article uses structured translations
from a in Cldr.Trans.Article, where: not is_nil(translated(Cldr.Trans.Article, a, :es)))
#=> SELECT a0."id", a0."title", a0."body", a0."translations"
#=> FROM "articles" AS a0
#=> WHERE (NOT (NULLIF((a0."translations"->'es'),'null') IS NULL))

More complex queries

The translated/3 macro can also be used with relations and joined schemas. For more complex examples take a look at the QueryBuilder tests (the file is located in test/trans/query_builder_test.ex).

Generates a SQL fragment for accessing a translated field in an Ecto.Query select clause and returning it aliased to the original field name.

Therefore, this macro returns a translated field with the name of the table's base column name which means Ecto can load it into a struct without further processing or conversion.

This macro delegates to the macro translated/3 and wraps the result in a fragment with the column alias.

See Cldr.Trans.QueryBuilder.translated/3 for more information.