How to create a query like udemy product page in ecto

[shahryarjb]

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 repo.one query which can load the map with list I need.

for example

%{
  course_info,
  headlines: %{
    [headline_info],
    chapters: %{
      [chapter_info]
    },
    works: %{
      [work_info]
    }
  }
}

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.

[shahryarjb]

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]
    Repo.all(query)
  end