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.

Probably you forgot to add

belongs_to(:member, Member)

to MemberAction schema

You can join in queries without explicit assocs in the schema, but there‘s no field on the schema struct to put data into, therefore preload does not work in such a case.

I had had already :member. And it works. I could not get prefecture.

ah, then preload: [member: :prefecture]

1 Like