Data is not being sent to my associated join table

zno · June 3, 2023, 9:48pm

Hi, elixir noob-ish person here haveing problems with sending data to a join table as part of our create secret function in elixir (elixir setup as an api service).

I have three tables,

user table
secret table
secret_user join table

The schemas are set as such

user schema

@primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "users" do
    field :username,    :string
    field :first_name,  :string
    field :last_name,   :string
    field :passphrase,  :string
    field :email,       :string
    field :phone_num,   :integer
    belongs_to(:org, KzyBeApi.Orgs.Org, foreign_key: :org_id, on_replace: :delete)
    many_to_many(:secrets, KzyBeApi.Secrets.Secret, join_through: KzyBeApi.Secrets_users.Secret_user)
    timestamps()
  end
  
  def changeset(user, attrs) do
    user
    |> cast(attrs,[
      :username,
      :first_name,
      :last_name,
      :passphrase,
      :email,
      :phone_num
    ])
    |> unique_constraint(:username)
    |> unique_constraint(:email)
    |> validate_required([:email, :passphrase])
    |> validate_format(:email, ~r/@/, message: "Email format not valid")
    |> validate_length(:email, max: 160)
    |> put_password_hash()
  end

secret schema

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "secrets" do
    field :secret_name,     :string
    field :external_login,  :string # username for credentials
    field :passphrase,      :string # passphrase for credentials
    field :file_name,       :string
    field :file_receiver,   :string
    field :created_date,    :string
    field :created_by,      :string # user_id table joiner foreign_key
    field :provider,        :string # 1Pass, AWS/ASM, LastPass, etc.
    field :s3_path,         :string
    field :token,           :string
    field :file_location,   :string
    field :updated_by,      :string
    field :secret_id, :string
    field :user_id, :string
    many_to_many :users, KzyBeApi.Users.User, join_through: KzyBeApi.Secrets_users.Secret_user
    many_to_many :secrets_users, KzyBeApi.Secrets_users.Secret_user, join_through: KzyBeApi.Secrets_users.Secret_user
    timestamps()
  end
  
  @doc false
  def changeset(secret, attrs) do
    secret
    |> cast(attrs, [
      :secret_name,
      :external_login,
      :passphrase,
      :file_name,
      :file_receiver,
      :created_date,
      :created_by,
      :provider,
      :s3_path,
      :token,
      :file_location,
      :updated_by,
    ])
    |> validate_required(:secret_name)
  end
end

secrets_users join schema

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "secrets_users" do
    belongs_to :secret, KzyBeApi.Secrets.Secret, foreign_key: :secret_id, on_replace: :delete
    belongs_to :user, KzyBeApi.Users.User, foreign_key: :user_id, on_replace: :delete


    timestamps()
  end

  @doc false
  def changeset(secret_user, attrs) do
    secret_user
    |> cast(attrs, [
      :user_id,
      :secret_id,
    ])
    # |> validate_required([:user_id, :secret_id])
  end
end

I have a build_assoc in the secrets_users.ex context file like so

def create_secret_user(secret, attrs \\ %{}) do
  secret
  |> Ecto.build_assoc(:secrets_users)
  |> Secret_user.changeset(attrs)
  |> Repo.insert()
end

Finally, I have a create function in the secret_controller.ex file as such

def create(conn, %{"secret" => secret_params}) do
  conn = Guardian.Plug.VerifyHeader.call(conn, realm: "Bearer")
  conn = Guardian.Plug.LoadResource.call(conn, allow_blank: true)
  conn = fetch_user_id_from_token(conn)

  changeset = Secret.changeset(%Secret{}, secret_params)

  case Repo.insert(changeset) do
    {:ok, %Secret{} = secret} ->
      map = %{:secret_id => secret.id, :user_id => conn.assigns[:user_id]}
      Secrets_users.create_secret_user(secret, %{secret_id: map[:secret_id], user_id: map[:user_id]})
        {:ok, secret_with_assoc} ->
          conn
          |> put_status(:created)
          |> render(:show, secret: secret_with_assoc)
        {:error, _changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{errors: "Failed to create the join record"})
    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{errors: changeset.errors})
  end
end

defp fetch_user_id_from_token(conn) do
  case Guardian.Plug.current_resource(conn) do
    nil -> conn
    resource -> assign(conn, :user_id, resource.id)
  end
end

This works as far as being able to make the api call and seeing the secret table get filled out

# SELECT * FROM secrets;
id                  | secret_name | external_login | passphrase | token | file_name | file_receiver | s3_path |     inserted_at     |     updated_at      
--------------------------------------+-------------+----------------+------------+-------+-----------+---------------+---------+---------------------+---------------------
 85342b3f-9bac-4fff-b489-93c5cad246c4 | secret name | somename       | testpass1  |       |           |               |         | 2023-06-03 21:16:21 | 2023-06-03 21:16:21

But I do not see the data hitting the join table

# SELECT * FROM secrets_users;
id | user_id | secret_id | inserted_at | updated_at 
----+---------+-----------+-------------+------------
(0 rows)

and I get this api response from the call as a response

{
  "errors": {
    "secret_id": [
      "is invalid"
    ],
    "user_id": [
      "is invalid"
    ]
  }
}

In the termial running mix phx.server for my api I see no errors and can confirm the values for user_id and secret_id thats suppose to get sent to the secrets_users join table is correct

warning: unused alias Secret_user
  lib/kzy_be_api_web/controllers/secret_controller.ex:5
[info] POST /api/secret/create
[debug] Processing with KzyBeApiWeb.SecretController.create/2
  Parameters: %{"secret" => %{"external_login" => "somename", "passphrase" => "testpass1", "secret_name" => "secret name"}}
  Pipelines: [:api, :auth]
[debug] QUERY OK source="guardian_tokens" db=4.2ms idle=1363.7ms
SELECT g0."jti", g0."typ", g0."aud", g0."iss", g0."sub", g0."exp", g0."jwt", g0."claims", g0."inserted_at", g0."updated_at" FROM "guardian_tokens" AS g0 WHERE ((g0."jti" = $1) AND (g0."aud" = $2)) ["723d6ea0-f29c-497c-a15b-b96d45c40e4a", "kzy_be_api"]
↳ Guardian.DB.on_verify/2, at: lib/guardian/db.ex:134
[debug] QUERY OK source="users" db=3.1ms idle=1368.0ms
SELECT u0."id", u0."username", u0."first_name", u0."last_name", u0."passphrase", u0."email", u0."phone_num", u0."org_id", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) ["e9763d23-63b0-4f3a-a1a4-1442ac5b8beb"]
↳ KzyBeApiWeb.Auth.Guardian.resource_from_claims/1, at: lib/kzy_be_api_web/auth/guardian.ex:20
[debug] QUERY OK source="users" db=3.9ms idle=1371.2ms
SELECT u0."id", u0."username", u0."first_name", u0."last_name", u0."passphrase", u0."email", u0."phone_num", u0."org_id", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) ["e9763d23-63b0-4f3a-a1a4-1442ac5b8beb"]
↳ KzyBeApiWeb.Auth.SetUser.call/2, at: lib/kzy_be_api_web/auth/set_user.ex:18
[debug] QUERY OK source="users" db=0.9ms idle=1374.7ms
SELECT u0."id", u0."username", u0."first_name", u0."last_name", u0."passphrase", u0."email", u0."phone_num", u0."org_id", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) ["e9763d23-63b0-4f3a-a1a4-1442ac5b8beb"]
↳ KzyBeApiWeb.Auth.Guardian.resource_from_claims/1, at: lib/kzy_be_api_web/auth/guardian.ex:20
[debug] QUERY OK db=2.4ms queue=1.4ms idle=1375.8ms
INSERT INTO "secrets" ("external_login","passphrase","secret_name","inserted_at","updated_at","id") VALUES ($1,$2,$3,$4,$5,$6) ["somename", "testpass1", "secret name", ~N[2023-06-03 21:28:46], ~N[2023-06-03 21:28:46], "189da04e-bbcf-4310-9460-241d6b182276"]
↳ KzyBeApiWeb.SecretController.create/2, at: lib/kzy_be_api_web/controllers/secret_controller.ex:24
Hello
189da04e-bbcf-4310-9460-241d6b182276
e9763d23-63b0-4f3a-a1a4-1442ac5b8beb
[info] Sent 422 in 26ms

I am lost at this point as to how to fix this or what my oversight is. ANY help is appreciated.

al2o3cr · June 3, 2023, 10:41pm

zno:

Finally, I have a create function in the secret_controller.ex file as such

def create(conn, %{"secret" => secret_params}) do
  conn = Guardian.Plug.VerifyHeader.call(conn, realm: "Bearer")
  conn = Guardian.Plug.LoadResource.call(conn, allow_blank: true)
  conn = fetch_user_id_from_token(conn)

  changeset = Secret.changeset(%Secret{}, secret_params)

  case Repo.insert(changeset) do
    {:ok, %Secret{} = secret} ->
      map = %{:secret_id => secret.id, :user_id => conn.assigns[:user_id]}
      Secrets_users.create_secret_user(secret, %{secret_id: map[:secret_id], user_id: map[:user_id]})
      # SOMETHING IS MISSING HERE
        {:ok, secret_with_assoc} ->
          conn
          |> put_status(:created)
          |> render(:show, secret: secret_with_assoc)
        {:error, _changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{errors: "Failed to create the join record"})
    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{errors: changeset.errors})
  end

There’s something odd in the code you posted, around where I’ve added the SOMETHING IS MISSING HERE comment: it’s indented like there should be another case expression, but there isn’t one. Is that on purpose, or a copy-paste error?

If that’s the code that is actually running, that would mean your create function’s last line in the {:ok, %Secret{}} code path is actually Secrets_users.create_secret_user - and returning an error tuple from a controller action will pass control to the fallback controller which usually renders a 422 and an errors hash.

HOWEVER

There’s a bigger underlying issue: Secrets_users.changeset is giving validation errors on cast. The generic is invalid message is what happens when the type passed in doesn’t cast to the type given in the schema.

The type being passed in is binary_id, as shown by the debug prints.

The type expected is :id, since Secrets_users is missing the @foreign_key_type :binary_id attribute

zno · June 3, 2023, 11:27pm

Hi @al2o3cr , yeah I had a case Secret_user.create_secret_user() do line there and tried to see if the placement of it was causing the error I’m seeing. But to no avail. I see what you’re saying about the fallback action and error. I’m still trying to figure out how to make the commit to the joiner table. I understand the concept of assoc statements and how they work. Just not how to cleanly implement them. :-/ this has been the bane of my existence for the last three days and I know I’m a line or two away from correcting the param format or param handling error causing this to break.

zno · June 4, 2023, 5:13pm

@al2o3cr thank you for your help. The feedback you provided was invaluable and I was able to fix my code and get this working correctly.

We were indeed missing the @foreign_key attribute from the schema for the join table as well as the “add :id, :uuid, primary_key: true” field for the migration table. Also made sure all of the involved schemas and migration table followed the :binary_id type or :uuid.

re-added the case line and blam, working.

def create(conn, %{"secret" => secret_params}) do
    conn = Guardian.Plug.VerifyHeader.call(conn, realm: "Bearer")
    conn = Guardian.Plug.LoadResource.call(conn, allow_blank: true)
    conn = fetch_user_id_from_token(conn)

    changeset = Secret.changeset(%Secret{}, secret_params)

    case Repo.insert(changeset) do
      {:ok, %Secret{} = secret} ->
        map = %{:secret_id => secret.id, :user_id => conn.assigns[:user_id]}
        case Secrets_users.create_secret_user(secret, %{secret_id: map[:secret_id], user_id: map[:user_id]}) do
          {:ok, secret_with_assoc} ->
            conn
            |> put_status(:created)
            |> render(:show, secret: secret_with_assoc)
          {:error, _changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{errors: "Failed to create the join record"})
      {:error, changeset} ->
        conn
        |> put_status(:unprocessable_entity)
        |> json(%{errors: changeset.errors})
      end
    end
  end