LSylvester
I have a query I want to run on a user object. I’m using Select to reduce the throughput from the database. Each user has a many to many relationship with departments. What I want to do is return a list of the department id’s in the select. So far, I have this:
context
|> join(:left, [u], ud in assoc(u, :user_departments))
|> select([u, ud], %{
id: u.id,
name: u.name,
email: u.email,
...
departments: ud.department_id
})
There are a lot more items called in my actual query. Obviously, the above fails because it returns only one id and errors if more than one are returned.
I’ve tried group_by, but unsuccessfully. Has anyone got a good example of this? I can’t find any Googling.
Thanks
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
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
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #ai
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #blog-post
- #elixirconf-us
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #elixirconf-eu
- #api
- #forms
- #security
- #metaprogramming
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!
Practical resources that improve the lives of professional developers.
Master modern tools, write cleaner code, and tackle real-world challenges.
Bringing learning, work and compliance together since 1987.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 5- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
idi527
Can you post the errors?
I think you can either use a
preloadin the query which will still be a join but it will collect the returned rows into some parent struct (user in your case, it seems), or use aRepo.preloadwhich would avoid a join but rather make a second request.How to Query a User with has_many Post in Ecto might be relevant.
LSylvester
I get a “field
departmentsinselectdoes not exist in schema” or “fielduser_departmentsinselectdoes not exist in schema”, even though the User schema has ahas_manyassociation touser_departmentsand amany_to_manyassociation todepartments.LSylvester
Okay, so I guess the answer is I can’t streamline it with a select, but instead I have to return the whole schema and relationships. That’s disappointing, but at least I can associate it with its SQL counterpart, now.
Many thanks @idi527, for your help.
idi527
I think you can write an ecto query that won’t require you to return unnecessary data …
MrSidecar
Yes, you can streamline the fields of the association.
try something along these lines (assuming a User and a many_to_many relation to departments):