Select_merge for field type `{:array, :map}`

Questions / Help
1

If you have a virtual field, say field(:users, {:array, :map}, virtual: true} what is the best way to populate that field with multiple records when querying the record? For example:

defmodule App.Topic do 
  schema "topics" do 
    field(:name, :string)
    field(:description, :string)
    field(:users, {:array, :map}, virtual: true)
  end 
end 

defmodule App.VirtualUser do 
   embedded_schema do 
      field(:name, :string)
      field(:id, :string)
   end 
end

In this case there is a bit of info from the user that is required by the topic but it may not make sense to load a full User struct for various reasons.

When querying a Topic I’d write something like this:

Topic 
|> from(as: :topic)
|> join(:left, [topic: t], u in assoc(t, :users), as: :user)
|> join(:left, [user: u], p in. assoc(p, :profile), as: :profile)
|> select_merge([topic: t, user: u, profile: p], 
   %{users: %VirtualUser{name: p.name, id: u.id}
)

Perhaps you can see the issue, this works when there is a single user, but when multiple users are associated with a Topic it breaks because select_merge/2 (I think), throws an error: ** (Ecto.MultipleResultsError) expected at most one result but got 2 in query which fair enough.

I’m by no means a Postgres guru or SQL in general for that matter, so what I’m attempting may not be possible but if theres a way of loading/associating multiple records on the record I’m very interested.

Further info: in this case preloads aren’t an option because my ultimate goal is to combine a few different types of records into the VirtualUser struct. The idea being that VirtualUser can be a uniform shape that I can load different records into (for example a User struct and a Guest struct could look very different from each other but each be merged into a VirtualUser struct.

2

I’m not sure I see the advantage of a super clever query here. If somebody has a suggestion I’m all ears but in the interim I ended up just preloading the required associations and then passing them to a function that adds VirtualUser unto the Topic struct:

Topic 
|> from(as: :topic)
|> preload([users: :profile])
|> Repo.all()
|> decorate_topics()

...

def decorate_topics(topics), do:  Enum.map(topics, &decorate_topic(&1))

def decorate_topic(%{users: users} = topic) do 
  virtual_users = Enum.map(users, & %VirtualUser{name: &1.name, id: &1.id})
  
 %{topic | users: virtual_users}
end

That’s the basic gist of it. In some ways I think this might even be more performant than an elaborate query.

3

Sounds like you’re getting Domain Driven Designy! I would say a new table-based schema for VirtualUser (as opposed to embedded) which in this case I would call Author. It could define only the fields you care about from the users table (and you could potentially remove author-specific fields form the User schema!). However, it sounds like you have more than one type of user table, or plan to? If this is the case then you would need to create a table view to combine them, so this is something you have to decide is worth it. I personally have no problem creating views while others may advice against it, so YMMV. In any event, doing so would allow you to preload and generally do regular query-type things in a standard way with a named domain concept (“authors”)!