I am trying to get back all the stories that are authored or coauthored by a particular author. I also want to preload all the authors onto that author’s stories, because that author might not be the only author of their story. Additionally, I want to select certain fields from both the story structs and the preloaded authors.

My head is getting a bit dizzy trying to work this out, because I am new to complex Ecto queries, rather than straightforward/simple ones. I went over the Ecto documentation many times, but some the more advanced options I have only really read about, not used (successfully) or understood fully.

Some more code context.

I am starting with a user variable that satisfies: %User{} = user. In the code below authors are also users (N.B. in this case all users are authors and vice versa, while in other parts of the application, that is not true). (Edit: Also, an author can have many stories, and a story can have many authors. Joined through table :authors_stories).

I have tried many queries, but I always got the job only partly done. I have tried, for example:

user_id = user.id

query =
  from u in User,
  where: u.id == ^user_id,
  join: s in assoc(u, :stories),
  join: a in assoc(s, :authors),
  select:
    %{
      id: s.id,
      title: s.title,
      authors: a
    }

user_stories = Repo.all(query)

But this does not return all authors of a story per row. Preloading the authors onto the stories made sense to me, but I can’t seem to select only certain author fields when preloading. With the following code snippet I get all fields from authors.

user_with_preloads = Repo.preload(user, [stories: :authors])
user_stories = user_with_preloads.stories 

The “preload queries” part of the documentation seemed maybe to be relevant, but was not able to make it work.

I would love to understand more complex querying, since it seems fundamental to me. So I am hoping to get some help tidying up some of the mess in my head around Ecto (and possible Elixir and SQL more generally). Any tips for this specific query and/or maybe books/blogs/courses, beyond the documentation, that are helpful to get a deeper understanding of the above?