Ecto associations and the purpose of has many through and many to many

I’m struggling to understand the purpose of has many through and the many to many associations. The 2 things I’m struggling with is inserting an associated record and querying through an association.

This is my schema setup:

schema "users" do
  field(:email, :string)
  field(:password, :string)

  has_many :users_profiles, UserProfile
  has_many :profiles, through: [:users_profiles, :profiles]
schema "profiles" do
  field(:description, :string)
  field(:is_active, :boolean)

  has_many :users_profiles, UserProfile
  has_many :users, through: [:users_profiles, :users]
schema "users_profiles" do
  field(:role, :string)
  belongs_to(:user, User)
  belongs_to(:profile, Profile)

When inserting a profile for a user a record needs to be added to the join table users_profiles. It seems to me that ecto doesn’t help here I couldn’t use put_assoc, cast_assoc or build_assoc so my solution was to use Ecto.Multi. Is this the correct way?

def create_profile(%User{} = user, attrs \\ %{}) do
  |> Multi.insert(:profile, Profile.changeset(%Profile{}, attrs))
  |>, fn _, %{profile: %{id: id}} ->
    UsersProfiles.create_user_profile_owner(%{user_id:, profile_id: id})
  |> Repo.transaction()

When querying using the association via Repo.preload it causes 2 queries. First way uses the has_many :profiles, through: [:users_profiles, :profiles]

user |> Repo.preload(:profiles) 

or alternatively using the has_many :users_profiles association and the belongs_to association on users_profiles schema.

user |> Repo.preload(users_profiles: [:profile])

So I instead just wrote a join query as that way it would only be a single query, like so:

def profiles(user) do
    query =
      from p in Profile,
        join: up in UserProfile,
        on: up.profile_id ==,
        where: up.user_id == ^


So I haven’t used the associations so far so I’m not sure of their purpose or maybe I’m doing things incorrectly? :man_shrugging:t2:

In Ecto’s has_many docs section on the :through option:

Although we used the :through association in the example above, Ecto also allows developers to dynamically build the through associations using the Ecto.assoc/2 function:

assoc(post, [:comments, :author])

In fact, given :through associations are read-only, using the Ecto.assoc/2 format is the preferred mechanism for working with through associations . Use the schema-based one only if you need to store the through data alongside of the parent struct, in specific cases such as preloading.

So, have you tried the assoc function?

1 Like

Just gave that a go and that it works and only runs 1 query to get a users profiles! Ok so there is a reason for those associations haha, thanks @dimitarvp

Any thoughts on how I approached inserting the association using Ecto.Multi?

This is the right way to use Ecto.Multi in general, yep, so good job there. It’s just that in this particular case there’s a better way. :wink: Outside of that though, Ecto.Multi is a very powerful tool.

1 Like

For a bit more context on the difference between those two options:


thanks @LostKobrakai this part cleared up the use of many_to_many for me:

It was added with the intent of hiding away the implementation detail of needing a join-table to create the relationship between the two many-to-many schemas. Because of that the join table can only support two foreign key columns and nothing else on the table.

@addstar would you mind sharing what your create_profile looks like now? I’m dealing with a similar situation, but not quite following how to apply assoc. Thanks!

1 Like

@rio517 my create_profile function is as per my first post still. I didn’t use assoc, I use multi to create the profile and then create the record in the join table. Hope this helps.