How to display records from tables

Phoenix Forum Questions / Help
,
1

Hello,
I have 2 tables.

connection.ex

  schema "connections" do
    field :comment, :string
    belongs_to :owner, Mdbms.Owner, foreign_key: :owner_id

    timestamps()
  end

  def test(connection) do
    from c in connection,
      left_join: owner in assoc(c, :owner)
  end

connections migration

  def change do
    create table(:connections) do
      add :owner_id, references(:owners, on_delete: :nothing)
      add :comment, :string

      timestamps()
    end

    create index(:connections, [:owner_id])
    create unique_index(:connections, [:id])
  end

owner.ex

  schema "owners" do
    field :first_name, :string
    field :last_name, :string
    has_one :connections, Mdbms.Connection

    timestamps()
  end

In my page_controller.ex I have

  def index(conn, _params) do
    connections = Connection
      |> Connection.test
      |> Repo.all
    render(conn, "index.html", connections: connections)
  end

I want to display in my template records both from connection table and owner table.
There’s code below which I’ve put in my page/index.html.eex but this way I can only display records from connection table and I have no idea how to display for instance “owners.first_name”.

<%= for connection <- @connections do %>
  <%= connection.comment %>
<% end %>
2

When I wrote and published a post I suddenly found a solution. I added to my query:

      select: %{
        test: c.comment,
        test2: o.first_name
      }

And it helped.

3

You can also preload the related tables into a struct. This means you run one query against the database, and then could reference connection.owner.first_name.

This would look something like:

query =
  from connection in Connection,
  inner_join: owner in assoc(connection, :owner),
  preload: [owner: owner]
connections = Repo.all(query)
3 Likes
4

Thank you very much. Your solution is the one I was looking for.