CODEWITHSUNDEEP
elixirphoenix-frameworkecto
tajihiro
tajihiro

Reputation: 2443

How to write ecto code with nested relation

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.

Upvotes: 1

Views: 628

Answers (2)

wiser
wiser

Reputation: 179

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)

Upvotes: 1

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121010

Use Ecto.Query.join/5.

query = from a in MemberAction,
  join: m in Member,
  on: [id: a.member_id]
  join: p in Prefecture,
  on: [id: m.prefecture_id],
  select: {a.action, m.member_name}

Upvotes: 1

Related Questions