Ecto + iex: how to display query results in tabular format?

Hello,

I’m going through the Ecto tutorial at: Querying · Elixir School

I would like to know if there’s an easy way to display query results in tabular form, in an iex shell. When iterating on queries, I would prefer to see the results that way, rather than looking at structs.

In rails land, the hirb gem captures the console output and does the job. Does anything similar exists with Elixir?

Digging a bit, I found the format_table() utility function provided by Ecto. It expects data of this shape:

iex(1)> %{columns: ["id", "title"], rows: [[1, "Ready Player One"], [2, "Inception"]]} |> Ecto.Adapters.SQL.format_table() |> IO.puts() 
+----+------------------+
| id | title            |
+----+------------------+
|  1 | Ready Player One |
|  2 | Inception        |
+----+------------------+

Which works great with this function call:

iex(2)> Repo.query("select m.id, title, tagline, ma.actor_id, a.name from movies as m INNER JOIN movies_actors as ma on ma.movie_id = m.id INNER JOIN actors as a on ma.actor_id = a.id") |> elem(1) |> Ecto.Adapters.SQL.format_table() |> IO.puts()

09:44:39.299 [debug] QUERY OK db=1.0ms queue=0.7ms idle=1309.3ms
select m.id, title, tagline, ma.actor_id, a.name from movies as m INNER JOIN movies_actors as ma on ma.movie_id = m.id INNER JOIN actors as a on ma.actor_id = a.id []
+----+------------------+-----------------------------+----------+----------------+
| id | title            | tagline                     | actor_id | name           |
+----+------------------+-----------------------------+----------+----------------+
|  1 | Ready Player One | Something about video games |        1 | Tyler Sheridan |
|  1 | Ready Player One | Something about video games |        2 | Gary           |
+----+------------------+-----------------------------+----------+----------------+

However, it’s rather cumbersome when using the Ecto.Query module:

iex(3)> %{columns: ["id", "title"], rows: Repo.all(from(m in Movie, where: m.id < 100, select: [m.id, m.title]))} |> Ecto.Adapters.SQL.format_table() |> IO.puts()
+----+------------------+
| id | title            |
+----+------------------+
|  1 | Ready Player One |
|  2 | Inception        |
+----+------------------+

Do you have any tips?

1 Like

You might be able to pipe into GitHub - djm/table_rex: An Elixir app which generates text-based tables for display

1 Like

Excellent ^^

I saw this lib initially, but thought the setup was too heavy for my needs.

On second thought, I managed to do this:

defmodule Db.Utils do
  def to_table(query_result) when is_list(query_result) do
    headers = hd(query_result).__struct__.__schema__(:fields)

    rows =
      Enum.map(query_result, fn struct ->
        Enum.map(headers, fn header -> Map.get(struct, header) end)
      end)

    TableRex.quick_render!(rows, headers)
    |> IO.puts()
  end

  def to_table(query_result) when is_struct(query_result) do
    headers = query_result.__struct__.__schema__(:fields)

    row = Enum.map(headers, fn header -> Map.get(query_result, header) end)

    TableRex.quick_render!([row], headers)
    |> IO.puts()
  end

  def to_table({:ok, %Postgrex.Result{} = query_result}) do
    Ecto.Adapters.SQL.format_table(query_result)
    |> IO.puts()
  end
end
$ cat .iex.exs
alias Friends.{Repo,Movie}
import Ecto.Query
import Db.Utils

Which allows this workflow:

iex(1) to_table from(m in Movie, where: m.id < 2) |> Repo.one
iex(2) to_table from(m in Movie, where: m.id < 100) |> Repo.all
iex(3) to_table Repo.query("select id, title from movies")

Pretty handy… Please let me know if you think of something better!