# `Cldr.Trans.QueryBuilder`
[🔗](https://github.com/elixir-cldr/cldr_trans/blob/v1.1.3/lib/cldr/trans/query_builder.ex#L2)

Provides helpers for filtering translations in `Ecto.Queries`.

This module requires `Ecto.SQL` to be available during the compilation.

# `translated`
*macro* 

# `translated`
*macro* 

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](Cldr.Trans.html#module-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](Trans.html#module-structured-translations)
and with [Free-form translations](Transl.html#module-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`).

# `translated_as`
*macro* 

# `translated_as`
*macro* 

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.

---

*Consult [api-reference.md](api-reference.md) for complete listing*