Field ID type in postgres vs integer type in Elixir

Phoenix Forum Questions / Help
,
#1

Hi I have the following error popping up whenever I try to delete a record. The error message is:

[info] GET /students
[debug] Processing with TutorWeb.StudentController.index/2
  Parameters: %{}
  Pipelines: [:browser]
[info] Replied phoenix:live_reload :ok
[debug] QUERY OK source="students" db=6.3ms decode=1.8ms queue=0.5ms
SELECT s0."id", s0."name", s0."inserted_at", s0."updated_at" FROM "students" AS s0 []
[info] Sent 200 in 127ms
[info] Replied phoenix:live_reload :ok
[info] DELETE /students/1
[debug] Processing with TutorWeb.StudentController.delete/2
  Parameters: %{"id" => "1"}
  Pipelines: [:browser]
[debug] QUERY OK db=0.3ms
begin []
[debug] QUERY ERROR source="sponsors_students" db=10.0ms
DELETE FROM "sponsors_students" AS s0 WHERE (s0."student_id" = $1) [%{}]
[debug] QUERY OK db=0.2ms
rollback []
[info] Sent 500 in 137ms
[error] #PID<0.439.0> running TutorWeb.Endpoint (connection #PID<0.431.0>, stream id 4) terminated
Server: 0.0.0.0:4000 (http)
Request: DELETE /students/1
** (exit) an exception was raised:
    ** (DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808..9223372036854775807, got %{}. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
        (postgrex) lib/postgrex/type_module.ex:713: Postgrex.DefaultTypes.encode_params/3
        (postgrex) lib/postgrex/query.ex:62: DBConnection.Query.Postgrex.Query.encode/3
        (db_connection) lib/db_connection.ex:1148: DBConnection.encode/5
        (db_connection) lib/db_connection.ex:1246: DBConnection.run_prepare_execute/5
        (db_connection) lib/db_connection.ex:540: DBConnection.parsed_prepare_execute/5
        (db_connection) lib/db_connection.ex:533: DBConnection.prepare_execute/4
        (ecto_sql) lib/ecto/adapters/sql.ex:546: Ecto.Adapters.SQL.execute!/4
        (ecto_sql) lib/ecto/adapters/sql.ex:538: Ecto.Adapters.SQL.execute/5
        (ecto) lib/ecto/multi.ex:579: Ecto.Multi.apply_operation/5
        (elixir) lib/enum.ex:1940: Enum."-reduce/3-lists^foldl/2-0-"/3
        (ecto) lib/ecto/multi.ex:563: anonymous fn/5 in Ecto.Multi.apply_operations/5
        (ecto_sql) lib/ecto/adapters/sql.ex:874: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
        (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4
        (ecto) lib/ecto/repo/transaction.ex:15: Ecto.Repo.Transaction.transaction/4
        (tutor) lib/tutor.ex:78: Tutor.delete_student/1
        (tutor) lib/tutor_web/controllers/student_controller.ex:51: TutorWeb.StudentController.delete/2
        (tutor) lib/tutor_web/controllers/student_controller.ex:1: TutorWeb.StudentController.action/2
        (tutor) lib/tutor_web/controllers/student_controller.ex:1: TutorWeb.StudentController.phoenix_controller_pipeline/2
        (tutor) lib/tutor_web/endpoint.ex:1: TutorWeb.Endpoint.instrument/4
        (phoenix) lib/phoenix/router.ex:275: Phoenix.Router.__call__/1

The relevant code files are:

student_controller.ex

def delete_student(id) do
    multi = Multi.new
    |> Multi.run(:sponsorstudent, fn Repo, id ->
       from(ss in "sponsors_students", where: ss.student_id == ^id)
       |> Repo.delete_all
       end)
    |> Multi.run(:student, fn Repo, id ->
      from(s in "students", where: s.id == ^id)
      |> Repo.delete_all
      end)
    

    case Repo.transaction(multi) do
      {:ok, _} -> IO.puts("all ok")
      {:error, _} -> IO.puts("not ok")
      {response, _result} -> IO.inspect |> IO.puts("Result: #{response}")
    end
  end

And this is the schema for the table whose records I’m trying to delete first in the above transaction:

defmodule Tutor.SponsorStudent do
  use Ecto.Schema
  import Ecto.Changeset

  schema "sponsors_students" do
    field :relationship, :string
    field :sponsor_id, :id
    field :student_id, :id
    field :sponsor_name, :string, virtual: true
    # field :remove, :boolean, virtual: true  # field to remove sponsor's association with student

    belongs_to :sponsors, Tutor.Sponsor
    belongs_to :students, Tutor.Student
  end

Anyone please have any idea, what have I not done or have messed up? Gosh, learning a framework is really saps out your energy :slight_smile:

Thank you.

#2

You start your function with def delete_student(id) do, so now id is bound to some student ID to delete.

You continue with Multi.run(:sponsorstudent, fn Repo, id ->, but there you are using the same variable again! So Elixir binds it in that function to a new value (empty map %{} as we can see from the error message). You do this in two places in fact.

Then you use the variable in a statement: where: ss.student_id == ^id, but this is using the latter bound id, so it will be turned effectively into where: ss.student_id == %{}, and that produces the error.

Don’t use the same variable names for different things in a function. This will only confuse you and produce hard to find errors. :slight_smile:

3 Likes
#3

Right, and you can see this in the debug logs:

DELETE FROM "sponsors_students" AS s0 WHERE (s0."student_id" = $1) [%{}]

The parameters list [%{}] shows that you’re passing in %{} as the parameter.

2 Likes
#4

Thank you so much. :pray: If not for help from folks like you I would have given up learning Elixir and it’s ecosystem. The few number of books are not really complete (but I can’t blame them as well, since there is so much to cover) and the documentation can be very confusing for a newbie like me.

1 Like