Phoenix Ecto中join与preload的区别及JOIN查询报错解决

Let's break this down step by step, starting with fixing your JOIN query issue, then explaining the core differences between JOIN and preload in Ecto.

Fixing Your JOIN Query #

The error you're seeing (maps cannot be converted to_param. A struct was expected) happens because your JOIN query returns plain maps instead of Ecto structs. Many parts of Phoenix (like route helper functions that rely on to_param) expect structs, not maps, to work correctly. Here are two solid ways to fix this:

Option 1: Return Member Structs with Prefecture Data #

If you want to keep working with Member structs (so you can use them with route helpers, forms, etc.), modify your query to select a Member struct and populate the prefecture association with just the data you need:

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

This returns full Member structs where the prefecture field is a minimal Prefecture struct containing only the prefecture_name. Your existing template code (<%= member.prefecture.prefecture_name %>) will work exactly as before, and you won't hit the to_param error anymore.

Option 2: Use Maps and Adjust Template/Routing #

If you prefer sticking with maps (e.g., for performance when you only need specific fields), you'll need to update any code that expects a struct. For example, if you use route helpers like member_path, pass the id explicitly instead of the whole map:

# Keep your query but add the id to the selected map
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

Then adjust your template's route references:

<!-- Before (causes error) -->
<%= link "Edit", to: member_path(@conn, :edit, member) %>

<!-- After (works with maps) -->
<%= link "Edit", to: member_path(@conn, :edit, member.id) %>

Core Differences Between JOIN and Preload in Ecto #

Now let's clarify why these two approaches behave so differently:

Preload #

• How it works: Preload runs two separate database queries: first it fetches all Member records, then it fetches all related Prefecture records in a single batch (avoiding the N+1 query problem), and Ecto "attaches" the prefectures to their members in memory.
• Returns: Full Member structs with the prefecture field populated with complete Prefecture structs.
• Best for: When you need complete structs for subsequent operations (like updating records, using form helpers, or passing to functions that expect Ecto structs). It's also simpler when you need all fields from the associated model.

JOIN #

• How it works: JOIN runs a single database query that combines data from both members and prefectures tables at the database level.
• Returns: Whatever you specify in the select clause—this can be maps, tuples, or partial/full structs.
• Best for: When you only need specific fields from both tables (reducing data transfer), when you need to filter results based on associated data (e.g., "only members from Tokyo"), or when you want to minimize the number of database queries.

内容的提问来源于stack exchange，提问作者tajihiro