Preload order_by a timestamp stored in join table?

Phoenix Forum Questions / Help
1

I have User many_to_many Class association. There’s a join schema Enrollment, that contains class_id, user_id, and also timestamp which tells me when the user enrolled.

Now I’m loading a Class and preloading all Users enrolled in it. I would like these users to be sorted by the time they enrolled, i.e. the column in join table.

This is what I came up with:

def get_class!(id) do
    class_users_query =
      from u in User,
      join: e in Enrollment,
      on: e.user_id == u.id,
      where: e.class_id == ^id,
      select: struct(u, [:id, :name, :email]),
      order_by: [asc: e.processed_at] # join so that we get e.processed_at
    class = Repo.get!(Class, id) |> Repo.preload(users: class_users_query)
    class
  end

It seems to work… But my questions are:

  1. isn’t it stupid complex and there’s some very obvious much simpler way to do this?
  2. if not, is there a way to inject that e.processed_at column into that select: struct(...)?

Thank you

2

  1. I don’t think it’s “stupid complex”… that’s many to many assoc =) May be doing in two sequential queries (one to fetch users, the other to fetch enrollments and then combine the results together manually) could be more efficient than one query with join. Gotta test it out…

  2. did you try

    select: struct(u, [:id, :name, :email, enrollments: [:processed_at]])

    (as seen in docs Ecto.Query.API.struct/2)

    if it doesn’t work we should be able to construct the map explicitly manually… something like:

    select: %{
  id: u.id, 
  name: u.name, 
  email: u.email, 
  enrollments: %{processed_at: e.processed_at}
}
2 Likes
3

Thank you!

How do you mean? If I need to sort users by the column in the enrollments table, then I can’t avoid a JOIN, can I?

And I ended up returning a map for now, couldn’t make the nested structs work, but that’s ok.

4

If I need to sort users by the column in the enrollments table, then I can’t avoid a JOIN, can I?

Right, the result will not come sorted from the DB. I think overall this JOIN is the way to go.

I just wanted to point out that sometimes dealing with many-to-many relationships single query with multiple joins could return a large amount of data to transfer over the wire to elixir, then multiple simple queries could be more efficient to retrieve data from the DB but again then both combining and sorting the results together should be done in application layer so it would probably compensate the time saved on transfer of large results from a single query :slightly_smiling_face:

I think those nested structs would work if we join with assoc and add preload enrollments to query… something like:

    from u in User,
      join: e in assoc(u, :enrollments),
      preload: [:enrollment],
      select: struct(u, [:id, :name, :email, enrollment: [:processed_at]]),
      order_by: [asc: e.processed_at],

Take a look at the examples of preloading nested associations Ecto.Query — Ecto v3.11.1

1 Like
5

You can also do only one DB query and just sort the results with Elixir code. Nothing wrong with that unless it’s something that has to run several tens of times a second.

6

How do you mean? I need to add that processed_at key somehow, so I need the join, right? Because when I query just the class with associated users, I don’t get that key.

7

seems like this tiny bit throws me could not find association "enrollments" on schema Enrol.Accounts.User. I would probably need to change many_to_many to has_many_through or something, right?

But I’m actually fine just returning the map. It made me think why would I actually need the whole struct? – If I understand correctly, I’d need it basically only if I wanted to do some edits to the class users, right? Which I don’t. I just add/remove users to class.

1 Like
8

I actually changed the returned map to a struct %EnrolledUser{} with enforced fields - the idea is to have some enforced structure that has to be exchanged between ecto and this particular show page. That show page contains a liveview with the list of users, so I also don’t need to add everything into the socket (e.g. hashed_password or user timestamps).

Is this a common approach, or am I overcomplicating things here?

2 Likes
9

Yeah, I didn’t know how your schema relationships are set.

Is this a common approach, or am I overcomplicating things here?

I don’t know if which one would be a “common approach”. But I would say if pattern matching on struct name could help, or we if we could benefit from explicitly enforced structure, as you mentioned - then structs are fine. Otherwise, I would go with “simpler” maps.

My rule of thumb is internally in core domain I often make use of structs as implementation detail, but on higher “api” level return a map. So that other components do not care about struct names.

1 Like
10

I’m a bit late to this conversation but if you want to set a default sort using a join schema, you can do it in your Class schema definition, like this:

schema "classes" do
  has_many: :enrollments, Enrollment, foreign_key: :class_id, preload_order: [asc: :processed_at]
  has_many: :users, through: [:enrollments, :user]
end

schema "users" do
  has_many: :enrollments, Enrollment, foreign_key: :user_id
  has_many: :classes, through: [:enrollments, :class]
end

schema "enrollments" do
  belongs_to: :user, User
  belongs_to: :class, Class
end

The preload_order is what does the ordering.

I always prefer to use this approach instead of using many_to_many.

1 Like