CODEWITHSUNDEEP
elixirecto
ardhitama
ardhitama

Reputation: 1992

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?

Upvotes: 2

Views: 657

Answers (2)

Justin Wood
Justin Wood

Reputation: 10061

You can use type/2.

from u in User, where: u.id == type(^"5", :integer)

Which generated the following query

SELECT u0."id", u0."first_name", u0."last_name", u0."email", u0."encrypted_password", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1::integer) [5]

Upvotes: 2

Dogbert
Dogbert

Reputation: 222358

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)

Upvotes: 1

Related Questions