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

Right on, nice work

What a timing . I just figured that out . Thank you still.

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.

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])

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’?

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?

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.

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.

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?

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])