Getting stuck on more complex Ecto queries: combining conditions, preloading and selecting

Phoenix Forum Questions / Help
, ,
1

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?

2

Can you post an example of how your tables look, or at least the foreign keys that link the entities?

3

Yes, or course. I should have mentioned explicitly that there is a many-to-many relationship between :users and :stories. That is, an author can have many stories, and a story can have many authors.

create table(:users, primary_key: false) do
  add :id, :uuid, primary_key: true
  add :username, :string
  ...

  timestamps()
end

create table(:stories, primary_key: false) do
  add :id, :uuid, primary_key: true
  add :title, :string
  ...

  timestamps()
end

create table(:authors_stories) do
  add :author_id, references(:users, type: :uuid, on_delete: :delete_all)
  add :story_id, references(:stories, type: :uuid, on_delete: :delete_all)
end

create unique_index(:authors_stories, [:author_id, :story_id])
4

I got a step closer just now with:

author_query =
  from u in User,
  select: u.username

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

It returns the stories associated with a user including a list of all author names. It fetched all story fields, though, instead of a selected few.

How would I take that last step?

5 
user_id = user.id

query =
  from s in Story,
  join: a in assoc(s, :authors), on: a.id == ^user_id,
  preload: [:authors]

It’s usually easier to start your query with what you want returned and then figure out how to do the filtering: you want stories, so from s in Story. Rather than starting with how you are going to filter and then figuring out how to get what you want returned.

2 Likes
6

I have been reading the Little Ecto Cookbook since a few days. It is been very helpful.

There is also the Pragmatic Programmers book Programming Ecto. Can it be recommended in 2023 as an educational source for building a strong fundamental understanding/mastery of Ecto? It was first published four years ago.

Edit: I have been learning SQL, because I don’t want to just jump into ECTO without haven taken that step also. I definitely don’t have an extensive background in using SQL, though.

7

Makes sense. Ty.

So used your suggestion and added the author_query to the preload.

    user_id = user.id

    author_query =
      from u in User,
      select: u.username

    query =
      from s in Story,
      join: a in assoc(s, :authors), on: a.id == ^user_id,
      preload: [authors: ^author_query],

    Repo.all(query)

This get me almost where I want to be. The query still returns too many fields from the story struct. Besides the authors, which are preloaded, I only need a small number of fields (e.g., id and title).

Doing the following is not possible though:

    query =
      from s in Story,
      join: a in assoc(s, :authors), on: a.id == ^user_id,
      preload: [authors: ^author_query],
      select: [s.id, s.title, s.authors]

It raises: the binding used in 'from' must be selected in 'select' when using preload in query and also field 'authors' in 'select' is an association in schema Letterhead.Stories.Story in query.

Why is that? And what would be the correct way of only getting back the desired fields?

8

Is this because preloading authors does not make it a field of stories? So it cannot be selected? Indeed authors is not a field of struct Story, since its many-to-many relationship is established through the authors_stories table which stores the foreign keys. However, authors is returned as a field of a story after Repo.all(query).

However, why must binding ‘s’ be selected? Why all of ‘s’?

9

I’m not sure, but my guess is it needs the struct in order to figure out what to preload.

What if instead of:

select: [s.id, s.title, s.authors]

You used:

select: struct(s, [:id, :title])
1 Like
10

I got there.

    user_id = user.id

    author_query =
      from u in User,
      select: %{id: u.id, username: u.username, verified: u.verified}

    query =
      from s in Story,
      join: a in assoc(s, :authors), on: a.id == ^user_id,
      preload: [authors: ^author_query],
      select: [:id, :title]

    Repo.all(query)

I confused select: [s.id, s.title] with select: [:id, :title]. This is starting to make more sense now, since select: [:id, :title] returns a struct and select: [s.id, s.title] does not. Reckon Ecto relies on the struct to do its work preloading data.

2 Likes
11

What a timing :sweat_smile: . I just figured that out :arrow_up:. Thank you still. :pray:

12

Right on, nice work