CODEWITHSUNDEEP
elixirecto
harryg
harryg

Reputation: 24107

Casting `sum` result to an integer in Ecto

I have a query in Ecto the includes the sum of a column containing integers. I'm using MySQL as the database.

e.g. 

result = Repo.one(
  from v in Vote,
    where: [post_id: 1],
    select: sum(v.direction)
)

IO.inspect(result)

# Yields: #Decimal<5>

The result is returned from Ecto as a #Decimal<x>. When I encode this to json it's cast to a string. Ideally I'd like it as an integer, especially as the result will always be an integer from the database.

What's the best way to cast this?

Here's my schema:

schema "votes" do
  field :direction, :integer

  belongs_to :user, Linklet.User
  belongs_to :link, Linklet.Link

  timestamps()
end

I have managed to achieve the intended result by using MySQL's CONVERT function within a fragment, but it doesn't seem like the most robust approach:

result = Repo.one(
  from v in Vote,
    where: [post_id: 1],
    select: fragment("CONVERT(?, SIGNED)", sum(v.direction))
)

Is there a better way?

Upvotes: 2

Views: 2194

Answers (2)

kuiro5
kuiro5

Reputation: 1601

You can cast to an integer at the database level using Ecto.Query.type/2:

result = Repo.one(
  from v in Vote,
  where: [post_id: 1],
  select: type(sum(v.direction), :integer)
)

Upvotes: 5

Dogbert
Dogbert

Reputation: 222348

SUM in MySQL returns a DECIMAL value for integer inputs.

The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).

Source

So you have two options: either convert the value to SIGNED like you're already doing, or convert the Decimal value to an integer using Decimal.to_integer/1 in Elixir.

Upvotes: 3

Related Questions