Selecting Fields From a many_to_many Link Table

Questions / Help
1

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)?
1 Like
2

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 :stuck_out_tongue:

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).

5 Likes
3

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.

1 Like
4

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 :slight_smile:

2 Likes