Using a lateral join to get N results from assoc per ID in list. Limit not working for inner query. What am I doing wrong?

Questions / Help
,
1

Let’s say I have a DB With Chats, Users, Subscriptions(link between User and Chat), and User’s have an associated Avatar (which is a struct/db table, rather than a string on the user). What I want to return, for a list of chat_ids, is 5 avatars for each chat (or less if there are less subscribers).

I want a result something like this:

%{
  chat_id1 => [
    %Avatar{},
    %Avatar{},
    %Avatar{},
    %Avatar{},
    %Avatar{},
  ],
  chat_id2 => [
    %Avatar{},
    %Avatar{},
  ],
  chat_id3 => [],
  ...
}

I have a query that works, and returns exactly what I want, EXCEPT the limit function isn’t being upheld, and it’s returning an Avatar for ALL subscribers to a chat.

Here’s my current function:

from(c in Chat,
      as: :chat,
      where: c.id in ^chat_ids,
      join: u in assoc(c, :subscribers),
      as: :user,
      join: a in assoc(u, :avatar),
      inner_lateral_join:
        top_five in subquery(
          from(User,
            where: [id: parent_as(:user).id],
            order_by: [desc: :inserted_at],
            limit: 5,
            select: [:id]
          )
        ),
      on: top_five.id == u.id,
      group_by: [c.id],
      select: {c.id, fragment("json_agg(?)", a)}
    )
    |> Repo.all()
    |> Enum.map(fn {chat_id, avatars} ->
      {chat_id,
       Enum.map(
         avatars,
         &struct(Avatar, &1 |> Map.new(fn {k, v} -> {String.to_atom(k), v} end))
       )}
    end)
    |> Enum.into(%{})

Any idea what I can change to fix this? Am I going about this the wrong way with a lateral join (SO seemed to suggest this was the best way to achieve what I want, but maybe I’ve converted the SQL to Ecto incorrectly).

Thanks in advance.