Casting `sum` result to an integer in Ecto

1
votes

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?

2
elixirecto
If you want it to be integer why would not you store in the database as integer in the first place? Also, please show your schema file for Vote. - Aleksei Matiushkin
Decimal.to_integer(result)? - Dogbert
@mudasobwa the direction column is an integer. I'm not sure why the result is a decimal. Schema posted - harryg
@Dogbert ideally I'd like the type-casting to occur at query time to avoid having to map over the result. - harryg
yes, it's also declared as an integer in the migration. I've checked the database itself and it is an integer. Maybe it's a mysql thing... - harryg

2 Answers

3
votes

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.

1
votes

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)
)