What is the difference between join and preload on Phoenix Ecto

Question

I'm confused about how to use ecto in phoenix.

My schemas are:

schema "members" do
    field :first_name, :string
    field :last_name, :string
    belongs_to :prefecture, Prefecture

    timestamps()
  end

  schema "prefectures" do
    field :prefecture_name, :string
    has_many :members, Member

    timestamps()
  end

The preload that works:

def list_members do
    Repo.all(Member)
      |> Repo.preload(:prefecture)
  end

And in templates can be used as:

<%= for member <- @members do %>
    <tr>
      <td><%= member.last_name %></td>
      <td><%= member.first_name %></td>
      <td><%= member.perfecture.prefecture_name %></td>
    </tr>
<% end %>

Now when using a query it won't work:

def list_members do
    query = from m in Member,
            join: p in Prefecture, on: p.id == m.prefecture_id,
            select: %{last_name: m.last_name , first_name: m.first_name, prefecture_name: p.prefecture_name}
    Repo.all(query)
  end

In templates is used the following way:

<%= for member <- @members do %>
    <tr>
      <td><%= member.last_name %></td>
      <td><%= member.first_name %></td>
      <td><%= member.prefecture_name %></td>
    </tr>
<% end %>

It causes the following error:

maps cannot be converted to_param. A struct was expected, got: %{first_name: "Boo", last_name: "Muu", prefecture_name: "Japan"}

What is difference and what I should do for it to work? I would like to use JOIN way. (2)

Answer 1

It's not that it doesn't work. Like the error indicates, the problem is with the format of the result.

The result of this code:

Repo.all(Member) |> Repo.preload(:prefecture)

Is a list of %Member{} structures. Whereas the result of your other query, is a list of maps which only contain what you selected.

Hence, the error. It comes from the URL helper that you're using:

<%= link "Show", to: Routes.member_path(@conn, :show, member) %>

These URL helpers can build a URL from a struct, according to what they should use (id, slug, etc.). But the point is that they expect a struct. If you want to use the helper with the result from the second query, you will have to pass the id directly to the URL helper Routes.member_path instead of the whole member struct. Like:

<%= link "Show", to: Routes.member_path(@conn, :show, member.id) %>

For this, you will need to add the member's id to your result map in your query:

def list_members do
  query = from m in Member,
          join: p in Prefecture, on: p.id == m.prefecture_id,
          select: %{id: m.id, last_name: m.last_name , first_name: m.first_name, prefecture_name: p.prefecture_name}
  Repo.all(query)
end

That's if you're using the id for the URLs (e.g. /members/:id), if you're using something else, you'll have to use that instead.