Best way to add a record to a large many_to_many association

Questions / Help
1

I’m struggling with a pretty simple database action, and I’m pretty sure I’ve missed something obvious…

Let’s say I have the following schemas:

defmodule User
  use Ecto.Schema
  
  schema "users" do
    field :username, :string
    many_to_many :pages, Page, join_through: "users_pages"

    timestamps()
  end
end

defmodule Page
  use Ecto.Schema
  
  schema "pages" do
    field :title, :string
    many_to_many :users, User, join_through: "users_pages"

    timestamps()
  end
end

Pages are added after the User is created. So from what I’ve seen in the docs and in blog posts, I would create a changeset func that uses put_assoc like:

def changeset(%User{} = user, attrs) do
  book
  |> cast(attrs, [:username])
  |> validate_required([:username])
  |> put_assoc(:pages, attrs.pages)
end

Then I can go:

User.changeset(user, %{pages: [page]}) |> Repo.update()

All good. Now sometime later, a User adds another Page. From what I’ve read, to add this new Page to the User, I need to provide the entire new pages list as a changeset. Something like:

user = Repo.get(User, 1) |> Repo.preload(:pages)
User.changeset(user, %{pages: [new_page | user.pages]}) |> Repo.update()

But then, what about when a user already has thousands of pages. It doesn’t make sense to me to fetch all data for all of those pages simply to insert a new one.

Is there a simple way to insert a new Page for a User, utilizing the safety (validations etc) of a changeset, without fetching all Pages for a User first?

2

Well, you can do this one of two ways

1.) Add the user to the page, rather than the page to the user. Relationships can be added in either direction. So you could end up with something like

Page.changeset(page, %{user: user})

If the page is new then it won’t have any associations, so you avoid having to load a lot of associations.

2.) Don’t use put_assoc/cast_assoc, instead just deal with ids. I wrote a short blog post on this, and I find it simpler. Instead of loading all the records, I validate the ids and use foreign_key_constraint

3

Hmm, well, yes! #2 sounds like the way to go actually. I couldn’t consistently say if a Page or User would have the lower number of associations. So to keep it simple #2 sounds pretty nice. I think I would need to create a schema for the join table, or perhaps delve into schemaless changesets and inserts.

I also read your blog post which made things very clear. Thank you!

1 Like