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

Questions / Help
1

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]
end

schema "profiles" do
  field(:description, :string)
  field(:is_active, :boolean)

  has_many :users_profiles, UserProfile
  has_many :users, through: [:users_profiles, :users]
end

schema "users_profiles" do
  field(:role, :string)
  belongs_to(:user, User)
  belongs_to(:profile, Profile)
end

Inserting:
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.new()
  |> Multi.insert(:profile, Profile.changeset(%Profile{}, attrs))
  |> Multi.run(:user_profile, fn _, %{profile: %{id: id}} ->
    UsersProfiles.create_user_profile_owner(%{user_id: user.id, profile_id: id})
  end)
  |> Repo.transaction()
end

Querying:
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 == p.id,
        where: up.user_id == ^user.id

    Repo.all(query)
  end

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:

2 Likes
Many to many – once again… (extra data on the join table)
2

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
3

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?

4

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
5

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

10 Likes
6

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

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

7

@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
8

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

9

Hey there, I have been having a run around with has_many, through vs many_to_many in the context of associating several “tags” with a “post” (for instance). My initial schema was using has_many through and when I went to associate a few tags with a post using put_assoc, it didn’t work. After some reading, it looks like I needed to changing the association to many_to_many. I made the change and it worked fine. Now I’m reading your post and you’re advising against many_to_many. It seems that you assume with a many_to_many you don’t have a join table. But in my case, I definitely do… many_to_many join_through: “posts-tags”. Would you still advise against this schema setup? If so, how do I associate several tags with a post at one time? In other words, what’s a good alternative to put_assoc if you’re not using many_to_many?

10

Relating posts and tags it’s probably one of the places where many to many actually works well. Normally you don’t need additional properties on the relationship between tags and posts so it can be created automatically. Consider the example of e.g. relating organizations and users. Once you introduce roles you can no longer create the relationships without setting a role for the relationship or to use a more concrete term the membership. That data has to be set somewhere therefore you can no longer use put_assoc supplying only users to organisations (or the other way round) and have the join table values created automatically. You at that point need to explicitly handle creating the relationship.

2 Likes
11

Ahh, now I see what you’re saying. That makes sense. Thanks for the quick reply!

12

Which is? :wink:

13

Dude, been a while, I don’t remember what I had in mind. :003:

14

I see :smiley: Hoped for something like “you don’t have to create this record/transaction yourself. If you do this and that, then Ecto will take care of everything” :wink:

15

Come to think of it, I am 90% sure I meant Ecto.build_assoc/3 docs :thinking: