Elixir/Phoenix sum of the column

0
votes

I'm trying to get the sum of the particular column. I have a schema of orders, with the field total, that stores the total price. Now I'm trying to created a query that will sum total value of all the orders, however not sure if I'm doing it right. Here is what i have so far: 

def create(conn, %{"statistic" => %{"date_from" => %{"day" => day_from, "month" => month_from, "year" => year_from}}}) do

date_from = Ecto.DateTime.cast!({{year_from, month_from, day_from}, {0, 0, 0, 0}})
revenue = Repo.all(from p in Order, where: p.inserted_at >= ^date_from, select: sum(p.total))

render(conn, "result.html", revenue: revenue)
end

And just calling it like <%= @revenue %> in the html.eex. As of right now, it doesn't return errors, just renders random symbol on the page, instead of the total revenue.

I think my query is wrong, but couldn't find good information about how to make it work properly. Any help appreciated, thanks!

2
phoenix-frameworkelixir
Which version of Ecto are you using? – Gazler
Try Repo.one instead of Repo.all. – Dogbert
@Dogbert damn, can't believe I made this mistake, thanks! – Ilya

2 Answers

6
votes

Your query returns just 1 value, and Repo.all wraps it in a list. When you print a list using <%= ... %>, it treats integers inside the list as Unicode codepoints, and you get the character with that codepoint as output on the page. The fix is to use Repo.one instead, which will return the value directly, which in this case is an integer.

revenue = Repo.one(from p in Order, where: p.inserted_at >= ^date_from, select: sum(p.total))
4
votes

@Dogbert's answer is correct. It is worth noting that if you are using Ecto 2.0 (currently in release candidate) then you can use Repo.aggregate/4:

revenue = Repo.aggregate(from p in Order, where: p.inserted_at >= ^date_from, :sum, :total)