Search code examples
elixirecto

How to translate SQL `cast as` to Ecto DSL?

I have the following dummy query

SELECT * FROM dummy_table
ORDER BY CAST(number_in_string AS DECIMAL)

How to write that in Ecto?

Solution

  • You can order_by a fragment like this:

    fragment("CAST(? as DECIMAL)", dt.number_in_string)

    from(p in Post, order_by: [asc: fragment("CAST(? as DECIMAL)", p.id)]) generates the following query:

    SELECT p0."id", p0."title", p0."user_id", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 ORDER BY CAST(p0."id" as DECIMAL)