How to get DB values returned by raw sql with Ecto

Question

I have made a complicated SQL. so that I use raw query. I've already referred https://stackguides.com/questions/36042888/raw-sql-with-ecto But I still stacked.

query = "select item_id, sum(unit) amount from sales where item_id = ? "
result = Ecto.Adapters.SQL.query(Repo, query, [item_id])

However I have no idea how to retrieve DB values like Ecto results in general. I hope I get values like Map with key & value whatever.

The result is blow.

%Mariaex.Result{columns: ["item_id", "amount"], connection_id: nil, last_insert_id: nil, num_rows: 1, rows: [[42, #Decimal<4>]]}, :get, [])

Should I retrieve by myself or there are general and smart way? Hopefully, the last result is output for Phoenix templates html.

I'm trying to change result value to Map like this.

item_result =
  {:ok, result} ->
    rows = result.get(:rows)
    columns = result.get(:columns)
    Enum.zip(columns, rows)

But it is hard for elixir novice.

I find no better way in the documentation, so I would use pattern matching to assign the values of the rows to the variables you want. You could even write a helper function that does exactly what I think you want. (Transfer these results into maps). — Joe Eifert
Only I want to know now is how to get columns name and rows values. then put them together and change map. — tajihiro

tajihiro tajihiro · Accepted Answer · 2018-02-17T05:40:11

I figured out how to treat raw SQL with Ecto. I just divided problems into small parts.

(1) sum() returns Decimal value in case of mysql.

query = "select item_id, sum(unit) amount from sales where item_id = ? "
result = Ecto.Adapters.SQL.query(Repo, query, [item_id])

=> I need convert #Decimal value to #Integer using Decimal.to_integer/1 in Elixir.

(2) Ecto.Adapters.SQL.query returns {:ok, results}

=> I should have received by {:ok, results} as return value.

(3) Retrieving :columns and :rows

columns = results.columns
rows = results.rows

(4) Put these values colums and rows together

Enum.zip(columns, rows) |> Enum.into(%{})

How to get DB values returned by raw sql with Ecto

1 Answers