travisf
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.
Trending in Questions
Other Trending Topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #code-sync
- #discussion
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #ai
- #elixirconf-us
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #blog-post
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #elixirconf-eu
- #security
- #processes
- #api
- #forms
- #hex










Showing Posts 1 to 2- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
travisf
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
VirtualUserunto theTopicstruct:That’s the basic gist of it. In some ways I think this might even be more performant than an elaborate query.
sodapopcan
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 callAuthor. It could define only the fields you care about from theuserstable (and you could potentially remove author-specific fields form theUserschema!). 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”)!