How to remove/delete a many-to-many relationhip?

BenFenner

I’m relatively new to Elixir/Phoenix (I have much more familiarity with RoR) but I’ve made it quite far in my new application by following tutorials and the documentation.

I am building a sort of one-stop shop for user accounts, where users can be created, and given permissions to one or more web sites (AKA resources) that I work on. Users can have many resources they belong to, and resources have many users that belong to it, so I went with a many-to-many relationship.

I created the user_resources table in the database as a full table with an auto-generated ID. Since a user can’t belong to a resource twice (or vice versa), I’ve created what I believe is a good unique index.

defmodule MyApp.Repo.Migrations.CreateUserResources do
  use Ecto.Migration

  def change do
    create table(:user_resources) do
      add :user_id, references(:users)
      add :resource_id, references(:resources)
      add :inserted_by, :integer
      add :updated_by, :integer

      timestamps()
    end

    create unique_index(:user_resources, [:user_id, :resource_id])
  end
end

I also set up a schema for this relationship. I much prefer my relationships to be as fully fleshed out as possible, so that’s what I’ve done here.

defmodule MyApp.Account.UserResource do
  use Ecto.Schema
  import Ecto.Changeset


  schema "user_resources" do
    field :resource_id, :integer
    field :user_id, :integer
    field :inserted_by, :integer
    field :updated_by, :integer

    timestamps()
  end

  @doc false
  def changeset(user_resource, attrs) do
    user_resource
    |> cast(attrs, [:user_id, :resource_id, :inserted_by, :updated_by])
    |> validate_required([:user_id, :resource_id, :inserted_by, :updated_by])
  end
end

I can successfully add a User to a Resource in my controller like this:

  def add_user(conn, %{"resource_id" => resource_id, "user_id" => user_id}) do
    resource = Domain.get_resource!(resource_id)
    user = Account.get_user!(user_id)
    Domain.add_user(resource, user)

    conn
    |> put_flash(:info, "User added to resource successfully.")
    |> redirect(to: resource_path(conn, :show, resource_id))
  end

Which calls add_user in the Domain Context, which does the real work:

  def add_user(%Resource{} = resource, user) do
    resource
    |> Repo.preload(:users)
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:users, [user])
    |> Repo.update!
  end

This gives me a user_resources record in the database, and with the User in an Account Context, I have this function which gives me back a list of the users associated with the given resource:

  def list_resource_users(resource) do
    Repo.all(assoc(resource, :users))
  end

So far, so good.

Now I want to remove the association, so I’m following the advice of this post: Many-to-many associations in phoenix and ecto - #20 by idi527

That part of my resource controller looks like this:

  def remove_user(conn, %{"resource_id" => resource_id, "user_id" => user_id}) do
    Domain.remove_user(resource_id, user_id)

    conn
    |> put_flash(:info, "User removed from resource successfully.")
    |> redirect(to: resource_path(conn, :show, resource_id))
  end

And the function defined in the Domain Context looks like this:

  def remove_user(resource_id, user_id) do
    "user_resources"
    |> where(resource_id: ^resource_id)
    |> where(user_id: ^user_id)
    |> Repo.delete()
  end

However, when I initiate that code, I get this error:
function Ecto.Query.__changeset__/0 is undefined or private

I have tried many different things, and gotten a few small improvements, but I think there must be something very simple I’m missing that might stand out to someone more experienced?

I appreciate any help I can get, and I’ll try to provide more info as I work on things.

16 comments

#ecto

3 7587 16

2021-04-09 02:12:01 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

mikemccall

Repo.delete/2 expects a changeset_or_struct as the first argument. It looks like you’re piping an #Ecto.Query ( ie.queryable) into it. I believe delete_all/2 is what you’re looking for.

Post #1

BenFenner

I had to turn some strings to integers, but delete_all/2 did the trick!

Thank you very much Mike. Now I’ll see about making a thread about why I can’t add more than one user to a resource…

Cheers!

Post #2

kokolegorille

That might override when setting new users

Post #3

mikemccall

As @kokolegorille said put_assoc will set the users to whatever is in the list. ie. [user]. Or in other words remove any users not in that list. You will need to include all of the users when using put_assoc or manage the association separately for single records.

Something along the lines of:

resource = Repo.preload(resource, :users)
resource
|> Ecto.Changeset.put_assoc(:users, [user | resource.users])

This function should be used when working with the entire association at once (and not a single element of a many-style association) and using data internal to the application.
put_assoc/4

Post #4

BenFenner

Thanks for the help both of you.

I was aware that I needed to include all users when putting the association, but I just assumed this line that I’m using from the Ecto documentation accomplished that (because I’m a newb):

|> Repo.preload(:users)

Mike, your suggestion makes more sense than the documentation, so I’ll see what I can accomplish using it (or something like it). I’ll report back.

Post #5

BenFenner

I wasn’t in love with the presented solution, as it would seem there should be a cleaner, more Elixir-like(?) solution to the problem, but I got over that and now I can add more than one user to a resource.

I went with this:

  def add_user(%Resource{} = resource, user) do
    resource = Repo.preload(resource, :users)
    resource
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:users, [user | resource.users])
    |> Repo.update!
  end

Almost everything is working great now. There is only one last thing I was having trouble with, and if people are willing to help here, I’ll change the title of this thread to better describe what answers can be found here.

At first when I set up the relationship table in the migration, I had the inserted_by and updated_by fields as required fields:

defmodule MyApp.Repo.Migrations.CreateUserResources do
  use Ecto.Migration

  def change do
    create table(:user_resources) do
      add :user_id, references(:users)
      add :resource_id, references(:resources)
      add :inserted_by, :integer, null: false
      add :updated_by, :integer, null: false

      timestamps()
    end

    create unique_index(:user_resources, [:user_id, :resource_id])
  end
end

I had to abandon that restriction, and went with the migration you see in the first post (lacking null: false for those two fields) because when adding a user to a resource, I couldn’t figure out how to set those fields.
I would get errors like this:

ERROR 23502 (not_null_violation): null value in column "inserted_by" violates not-null constraint

    table: user_resources
    column: inserted_by

Failing row contains (1, 1, 1, null, null, 2018-09-18 13:14:18.86783, 2018-09-18 13:14:18.867834).

So I tried stuff like this as a test, which looked plausible, but did not work to set those fields before saving them to the database:

  def add_user(%Resource{} = resource, user) do
    resource = Repo.preload(resource, :users)
    resource
    |> Repo.preload(:users)
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_change(:inserted_by, 1)
    |> Ecto.Changeset.put_change(:updated_by, 1)
    |> Ecto.Changeset.put_assoc(:users, [user | resource.users])
    |> Repo.update!
  end

I tried the same thing but making the changes after the put_assoc but either ordering got me the exact same error as mentioned above. If I could get this working, my many-to-many relationship questions would all be solved.

Post #6

BenFenner

I’m starting to think what I’m trying doesn’t work because it is setting (or trying to set) the fields on the resource record, not the user_resource record…

I’m going to look into the options for put_assoc and see what I can find there.

Edit: Although it accepts an opts argument, there are no options currently supported by put_assoc/4. (cite)

Now I’m stumped again. =(

Post #7

mikemccall

I think this might be getting overly complicated. cast_assoc and put_assoc are nice but there’s a lot of “automation” and if you end up working with alot of association it’s probably overkill to load thousands of users just to enter one record. If the purpose add_user/2 in your code is to just create the association I would create the schema for :user_resource and simply use that.

Possible updated version of add_user/2

def add_user(%Resource{} = resource, %User{} = user) do
    attrs = %{user_id: user.id, resource_id: resource.id, inserted_by: user.id, updated_by: user.id}
    %UsersResource{}
    |> UserResource.changeset(attrs)
    |> Repo.insert_or_update!
  end

Post #8

BenFenner

That’s very helpful Mike. I do think I have much more complication than I need. Manually adding the record the way you’re suggesting should work perfectly. I already have a schema for the relationship (second code block in first post) so let me give this a try and see where I end up.

Post #9

BenFenner

I got things working, and then tweaked it a bit to pass IDs around instead of whole Structs.
And as a bonus, it looks like I don’t need to turn Strings into INTs explicitly anymore.
So here’s what I ended up with in the controller:

  def add_user(conn, %{"resource_id" => resource_id, "user_id" => user_id}) do
    current_user_id = Authentication.current_user(conn).id
    Domain.add_user(resource_id, user_id, current_user_id)

    conn
    |> put_flash(:info, "User added to resource successfully.")
    |> redirect(to: resource_path(conn, :show, resource_id))
  end

And in the Domain Context:

  def add_user(resource_id, user_id, current_user_id) do
    attrs = %{resource_id: resource_id, user_id: user_id, inserted_by: current_user_id, updated_by: current_user_id}
    %UserResource{}
    |> UserResource.changeset(attrs)
    |> Repo.insert_or_update!
  end

No doubt there are still issues with this. I believe URL manipulation could allow a user to send IDs through that don’t get verified as existing, and we could end up with relationships for Users and/or Resources that don’t exist. But I’m taking baby steps right now.

If there are other obvious things I could do better (maybe passing Structs around is preferred?), I’m open to suggestions.

I have enough working that I can focus on other parts now though.

Thank you Mike (and Koko) for all of your help. There must be a time limit on editing posts, as I can’t change the title of this thread. If I could I’d probably name it something like “How to add/remove customized many-to-many relationships.”. When people search in the future, maybe that text will help.

Post #10

Load more posts (6 remaining)