How to write ecto code with nested relation

Question

I have following DB table relation.

Prefecture 1 - 1 Member 1 - N MemberAction

I would like to get data, in case of SQL.

    SELECT A.action, M.member_name, 
      FROM MemberAction A
      JOIN Member M 
        ON M.id = A.member_id
      JOIN Prefecture P
        ON M.prefecture_id = P.id

However I have no idea to write code by Ecto. Following code does not work. Because of MemberAction does not have association

    query = from a in MemberAction,
            where: a.id == ^action_id,
            preload: [:member, :prefecture]
    Repo.all(query)

Please give me some advice.

Thanks.

Answer 1

How did you define your schemas? Make sure they are similar to the following:

defmodule YourApp.Schema.Prefecture do
  use Ecto.Schema
  schema "prefectures" do
    # your fields
  
    has_one(:member)
  end
end

defmodule YourApp.Schema.Member do
  use Ecto.Schema
  schema "members" do
    # your fields
  
    belongs_to(:prefecture)
    has_many(:member_actions)
  end
end

defmodule YourApp.Schema.MemberAction do
  use Ecto.Schema
  schema "member_actions" do
    # your fields
  
    belongs_to(:member)
  end
end

And then you should be able to use a query that's very similar to yours

    query = from a in MemberAction,
            where: a.id == ^action_id,
            preload: [member: :prefecture]
    Repo.all(query)