How to create a query like udemy product page in ecto

Imagine you should create a page like udemy course or product page. now we have tables like

  • course
  • headline
  • chapter


I can’t create a query which can load the map with list I need.

for example

  headlines: %{
    chapters: %{
    works: %{

my table relation term:

lms_headlines -> lms_courses

has_many :lms_headlines, .HeadlineSchema ,  foreign_key: :course_id, on_delete: :nothing

(lms_chapters , lms_works) -> lms_headlines

has_many :lms_chapters, ChapterSchema ,  foreign_key: :headline_id, on_delete: :nothing
has_many :lms_works, WorkSchema ,  foreign_key: :headline_id, on_delete: :nothing

Please see this pic that likes udamy

in the end , how can I create a query that loads my course info and loads all headlines with their chapters which be useful for creating a lop for.

thank you.

I think I found what I needed, preload helps me again!!!

def headline_with_course_id(course_id) do
    query = from u in HeadlineSchema,
      where: u.course_id == ^course_id,
      join: c in assoc(u, :lms_chapters),
      join: j in assoc(u, :lms_works),
      preload: [lms_chapters: c, lms_works: j]