LawJolla
Selecting Fields From a many_to_many Link Table
I’ve looked for the duplicate answer. If I’ve missed it, please link it so I can delete this question.
In the Ecto docs many_to_many example, what’s the best way to get the timestamps returned from users_organizations when following the post/tags query example? e.g.
# Get all users for a given organization
organization = Repo.get(Organization, 1)
users = Repo.all(assoc(organization, :users))
# How to get timestamps from users_organization (when user joined organization)?
Marked As Solved
lukerollans
I think this is a great example of where join schemas shine. Assuming you have a schema defined as such…
defmodule YourApp.UserOrganization do
use Ecto.Schema
schema "users_organizations" do
belongs_to :organization, YourApp.Organization
belongs_to :user, YourApp.User
timestamps()
end
....
end
You could run a query like so
organization_id = 1
YourApp.UserOrganization
|> where([uo], uo.organization_id == ^organization_id)
|> preload(:user)
|> Repo.all
Disclaimer: I prefer the pipeline syntax for Ecto queries, feel free to switch over to from if you like 
Now, you have a list of UserOrganization structs which include the timestamps of when the user joined the organization. If you need to access the user itself, just use the assoc (which was preloaded above for demonstration).
Also Liked
lukerollans
Outside of naming each field in a select statement, is there a way to unnest the structs so it’s all at the same level, e.g.
Nope, by definition structs can only contain fields they’re defined with. It’s impossible for %UserOrganization.email to be a thing, unless you actually add it to the schema.
Glad it helped, though! Remember to mark it as solved so others can get help, too 
LawJolla
Thank you, it works perfectly!
I had tunnel vision. I thought that I had to start with Organization… because that’s the id I had. Query UserOrganization with the organization_id… duh. I’m very comfortable in SQL, so in many to many, I always think to search a table, join the link table, then join the other table.
Quick tangential question… your query returns a list of structs. Inside the structs are nested structs keyed by the schema name, e.g.
[
%UserOrganization{
inserted_at:...
user: %User{
email: "email"
...
}
}
]
Outside of naming each field in a select statement, is there a way to unnest the structs so it’s all at the same level, e.g.
[
%UserOrganization{
inserted_at: ...
email: "email",
...
}
]
Thank you again. Huge help.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
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.
Error tracking for Elixir devs who love to ship. Start your free account.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"