Rendering result of Ecto.Adapters.SQL.query

Phoenix Forum Questions / Help
,
1

Hi, I’m new to Elixir and Phoenix.

I’m doing some experiments with Ecto.Adapters.SQL.query and now I would like to render a query result into an HTML table.

So far I did this:

page_controller.ex:

  def hello(conn, _params) do                                                                                                                                                                   
    {:ok, result} = Ecto.Adapters.SQL.query(:"Elixir.Hello.repo", "select idstudy, modality from study order by idstudy", [])                                                                   
    render(conn, "hello.html", studies: result)                                                                                                                                                 
  end

hello.html.heex

<section class="phx-hero">                                                                                                                                                                      
  <h1>Hello!</h1>                                                                                                                                                                               
  <table>                                                                                                                                                                                       
  <%= for study <- @studies do %>                                                                                                                                                               
    <tr>                                                                                                                                                                                        
      <td><%= study.idstudy %></td>                                                                                                                                                             
      <td><%= study.modality %></td>                                                                                                                                                            
    </tr>                                                                                                                                                                                       
  <% end %>                                                                                                                                                                                     
  </table>                                                                                                                                                                                      
</section>

When I run that code I get this:

##### Protocol.UndefinedError <small>at GET</small> <small>/hello</small>

# protocol Enumerable not implemented for %Postgrex.Result{columns: ["idstudy", "modality"], command: :select, connection_id: 4334, messages: [], num_rows: 8, rows: [[12070, "CR"], [12071, "MR"], [12072, "CR"], [12073, "MR"], [12074, "CR"], [12075, "CR"], [12076, "CR"], [12077, "MR"]]} of type Postgrex.Result (a struct)
2

Postgrex.Result is a struct that contains returned rows in :rows field. Additionally it will not map returned columns to their values, so for study <- @studies, do: {study.idstudy, study.modality} will not work. You need to do mapping on your own.

Any particular reason to write your queries by hand instead of using schemas or schemaless Ecto.Query? Also why low case repo in :"Elixir.Hello.repo"? That would make your life much easier if the repo would be Repo.

1 Like
3

Could you point me to an example?.

Well, I don’t see the value of ORMs in general, I prefer to write my queries by hand instead of adding an abstraction layer to do the same in a slightly different language.

I created this test by following an example I found by googling for Ecto.Adapters.SQL.query (cannot find it right now). To fix it should I just replace “Elixir.Hello.repo” to “Elixir.Hello.Repo”?.

4

You might try something like this…

Enum.map(result.rows, &Map.new(Enum.zip result.columns, &1))

You also need to convert the resulting list of maps, to a list of your structs.

5

Ecto is not ORM, it is more of the query API with changesets.

Your query can be written as:

Repo.all(
  from "study",
    order_by: [idstudy: :asc],
    select: [:idstudy, :modality]
)

It is not much different from your hand-crafted SQL (and I would say that the QUEL-like order that Ecto.Query provides is much clearer than SQL “read from the middle, then end, and then beginning” order).

That will return you just list of maps, what is what you expected from the beginning. But why stop there? Instead of always remembering to define :select in query and remember what is the table name, you can “predefine” it:

defmodule Study do
  def table_name, do: "study"

  def fields, do: [:idstudy, :modality]
end

And then you can do:

Repo.all(
  from Study.table_name(),
    order_by: [idstudy: :asc],
    select: ^Study.fields()
)

Ok, but now we need to define Study twice, also if we have a lot of such tables, then the code will became repetitive, so Ecto has handy “schemas” which allow you to simplify all of that:

defmodule Study do
  use Ecto.Schema

  schema "study" do
    field :idstudy, :string
    field :modality, :integer
  end
end

Repo.all(
  from Study,
    order_by: [idstudy: :asc],
)

So as you see, there is nothing magical that does stuff behind your back. It is just small set of helpers to write queries faster and easier instead of manipulating strings. It also makes it much harder to accidentally have SQLi, as if you do something like:

input = "'; TRUNCATE TABLE study; --"
Repo.all(
  from Study,
    where: [idstudy: ^input]
)

It will send it as a prepared statement to the DB, which mean that passed argument will always be treated as properly escaped.

EDIT:

If I haven’t stated it strong enough QUEL >>>>>>>>>>>> SQL and do not even bother @-ing me, as there is nothing to discuss.

3 Likes
6

Great explanation, thank you! @hauleth .

7

@kokolegorille, by using Enum.map I end up with this:

defmodule HelloWeb.PageController do                                                                                                                                                            
  use HelloWeb, :controller                                                                                                                                                                     
                                                                                                                                                                                                
  def index(conn, _params) do                                                                                                                                                                   
    render(conn, "index.html")                                                                                                                                                                  
  end                                                                                                                                                                                           
                                                                                                                                                                                                
  def hello(conn, _params) do                                                                                                                                                                   
    {:ok, result} = Ecto.Adapters.SQL.query(:"Elixir.Hello.repo", "select idstudy, modality from study order by idstudy", [])                                                                                                                                                                                                                           
    data = Enum.map(result.rows, &Map.new(Enum.zip result.columns, &1))                                                                                                                         
    render(conn, "hello.html", studies: data)                                                                                                                                                   
  end                                                                                                                                                                                           
end     

<section class="phx-hero">                                                                                                                                                                      
  <h1>Hello!</h1>                                                                                                                                                                               
  <%= for study <- @studies do %>                                                                                                                                                               
    <%= for {key, value} <- study do %>                                                                                                                                                         
      <%= key %>                                                                                                                                                                                
      <%= value %>                                                                                                                                                                              
    <% end %>                                                                                                                                                                                   
  <% end %>                                                                                                                                                                                     
</section>
8

It’s ok for simple scenario, but if You need associations, it would be preferable to use @hauleth solution.

9

And if you have structs, you can use Ecto.Repo — Ecto v3.7.1 to load the queried results into a struct.

3 Likes
10

Even easier… thanks for the Repo.load tip :slight_smile: