Ecto.Adapters.SQL.query(Repo, "sql", []) return flat rows, not key value pairs

[W.M.]

When I use Ecto.Adapters.SQL.query(Repo, "sql", []) I get a result like this:

{:ok, %Postgrex.Result{columns: ["code", "expires", "id", "name"], command: :select, connection_id: 30856, num_rows: 2, rows: [["Three-001", {{2011, 1, 1}, {0, 0, 0, 0}}, 3, "Mike"], ["Three-002", {{2011, 1, 1}, {0, 0, 0, 0}}, 1, "Mike"]]}}

The `rows` object has flat lists, not key value pairs, a thing that makes using the values in the view harder. Is there a way to return the result with a format like this:

[
{code:"Three-001", expires:"01/01/2016", id=1, name="Mike"},
{code:"Three-002", expires:"01/01/2016", id=1, name="Mike"}
]

Thanks.

[José Valim]

You can create a function that does it for you. Using the colums and rows information. The query data is kept "raw" on purpose.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/486d7ec8-5de3-4a29-9e79-d8daca593e9f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paul Clegg]

Specifically, this function:

Enum.map rows, fn(row) ->

  Enum.zip(columns, row) |> Map.new

end

This doesn't handle the conversion of the tuple representation to a date, of course, but it will return an array of maps where each map has the column names as the keys (string keys, unless you atomize them before doing the zip), and you can then do what you want with the results.

...Paul

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4%2BHeLjuq%3D%3DoD9wko6ZcWOXKc4zR7dOzn5Ka7PO1EpLOQA%40mail.gmail.com.

[W.M.]

Thank you guys for the clarification. I must admit this is only my 3rd day into Elixir/Erlang/Phoenix :-) ..

So far, I created a map and displayed the desired values in the view. However, there are the view/edit links that I must pass for them struct, not map.. I get this error:

maps cannot be converted to_param. A struct was expected, got:

I am doing all of this because I couldn't achieve the desired results I need using normal ECTO query (I prefer, as in Rails to write normal SQL). I need to make joins and ECTO doesn't return the whole set of data I need.

Any ideas to improve my attempts :-) ? Thanks.

[José Valim]

Instead of doing user_url(conn, user), you can pass the id directly: user_url(conn, user.id) or whatever you want to use as a parameter.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/1a096693-dbd7-40a0-8c2b-b2b9ceaefdfe%40googlegroups.com.

[W.M.]

Thank you very much.

[W.M.]

I have create the function below which I pass to it the result of Ecto.Adapters.SQL.query!(,,,) and it returns the results:

def sql_magic(result_of_Ecto_Adapters_SQL_query) do
  result = Enum.map result_of_Ecto_Adapters_SQL_query.rows, fn(row) ->
  Enum.zip(result_of_Ecto_Adapters_SQL_query.columns, row) |> Map.new
end

The little missing piece of code for the function above should return the keys as atoms and not string. Would you please tell me how to do that? Thanks. 

[Paul Clegg]

def sql_magic(result_of_Ecto_Adapters_SQL_query) do

  columns = result_of_Ecto_Adapters_SQL_query |> Enum.map(&(String.to_atom(&1)))
  Enum.map result_of_Ecto_Adapters_SQL_query.rows, fn(row) ->
    Enum.zip(columns, row) |> Map.new

  end
end

Note that you don't need to assign the results to "result", the last call in the function has its value returned.

...Paul

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/687526fe-4738-4b21-bdfa-03f19c97c49a%40googlegroups.com.

[W.M.]

Thanks @...Paul for your time into this, I have tried the function definition you provided above but I am getting this error:

protocol Enumerable not implemented for %Postgrex.Result{columns: ["code", "expires", "id", "name"], command: :select, connection_id: 28274

[Paul Clegg]

You want to Enum.map over the rows in the Postgres.Result struct, not the struct itself.  Looks like I rushed that part, this line:

columns = result_of_Ecto_Adapters_SQL_query |> Enum.map(&(String.to_atom(&1)))

should be

columns = result_of_Ecto_Adapters_SQL_query.columns |> Enum.map(&(String.to_atom(&1)))

...Paul

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/7e1507fd-febe-45ae-a6ff-250697883b99%40googlegroups.com.

[W.M.]

Thanks.. Worked great.. Now I will read little elixir to see how to display the date properly after passing the date object through the magic_sql function :-)

[W.M.]

UPDATE, day 4 in Elixir/Phoenix :

a) Elixir/Phoenix is simply great. Looking forward for this framework to expand :-)

b) I have found my way to 

Ecto.DateTime.cast!(date/time tuple)

.. Works 100%

Thank you very much guys.

[matt baker]

where should i put Ecto.DateTime.cast!(date/time tuple) in function 

def sql_magic(result_of_Ecto_Adapters_SQL_query) do

  columns = result_of_Ecto_Adapters_SQL_query.columns |> Enum.map(&(String.to_atom(&1)))
  Enum.map result_of_Ecto_Adapters_SQL_query.rows, fn(row) ->
    Enum.zip(columns, row) |> Map.new

  end
end

[h ka]

I just put this here in case someone else stumble upon this.

 def sql_magic(result_of_Ecto_Adapters_SQL_query) do
    columns = result_of_Ecto_Adapters_SQL_query.columns |> Enum.map(&(String.to_atom(&1)))
    Enum.map result_of_Ecto_Adapters_SQL_query.rows, fn(row) ->

      map = Enum.zip(columns, row) |> Map.new
      with {:ok, time} <- Ecto.DateTime.cast(map.inserted_at) do
        Map.put(map, :inserted_at, to_string(time))
      else
        _ ->
          map
      end
    end
  end

I have assumed you want to get the `inserted_at` or any date.

Reference: https://hexdocs.pm/ecto/Ecto.DateTime.html#cast/1