Belongs_to assoc changeset not being inserted into database


@table :jobs
def change do
  create table(@table) do
    add :status_id, references(:job_statuses, column: "id", type: :string), nil: false

@table :job_statuses
def change do
  create table(@table, primary_key: false) do
    add :id, :string, primary_key: true
    common [:title, :description]


@primary_key {:id, :string, autogenerate: false}
schema "job_statuses" do
  common [:title, :description]


schema "jobs" do
  belongs_to :status, Qber.V1.JobStatusModel, foreign_key: :status_id, references: :id, type: :string

Changeset of Job

def changeset(struct, params) do
  |> cast(params, @required_fields ++ @optional_fields ++ [:status_id])

But when I try to create job with the status id passed from user, I got this error: **** (Postgrex.Error) ERROR 22P02 (invalid_text_representation): invalid input syntax for type json**

Token “confirmed” is invalid.
(ecto) lib/ecto/adapters/sql.ex:571: Ecto.Adapters.SQL.struct/7
(ecto) lib/ecto/repo/schema.ex:469: Ecto.Repo.Schema.apply/4
(ecto) lib/ecto/repo/schema.ex:205: anonymous fn/13 in Ecto.Repo.Schema.do_insert/4
(ecto) lib/ecto/repo/schema.ex:691: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
(db_connection) lib/db_connection.ex:1366: DBConnection.transaction_nested/2
(db_connection) lib/db_connection.ex:1226: DBConnection.transaction_meter/3
(db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
(ecto) lib/ecto/multi.ex:422: Ecto.Multi.apply_operation/5
(elixir) lib/enum.ex:1826: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/multi.ex:412: anonymous fn/5 in Ecto.Multi.apply_operations/5
(ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
(db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
(db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
(db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
(ecto) lib/ecto/repo/queryable.ex:21: Ecto.Repo.Queryable.transaction/4
(qber) web/V1/Job/controller.ex:16: Qber.V1.JobController.create/2
(qber) web/V1/Job/controller.ex:1: Qber.V1.JobController.action/2
(qber) web/V1/Job/controller.ex:1: Qber.V1.JobController.phoenix_controller_pipeline/2
(qber) lib/qber/endpoint.ex:1: Qber.Endpoint.instrument/4
(phoenix) lib/phoenix/router.ex:278:

I have spent quite some time on articles like these :belongs_to + custom primary keys
and How to set a belongs_to association
and Ecto schema on git repo
and relationship-in-ecto
and Problems with belongs_to association with custom primary keys
and similar articles. All other similar associations are being cast and inserted perfectly, only this one having customer key is throwing exception. please help me solve this issue

What’s common?

Its a macro which will add these fields provided to it in schema

Can you post it? Judging by the error, the problem might be with your schema definition.

Also it might be helpful to see the data that Qber.V1.JobController.create provides to the changeset.

def create(conn, %{@singular => body}) do
			changeset = @model
			|> struct
			|> @model.changeset(body)
			case @model.insert(changeset) do
				{:ok, record} ->
					success(conn, record)
				{:error, changeset} ->
					error(conn, changeset)

I tried without Common macro. So there is no problem in macro or the controller function. Other belongs_to relations which invlolve default id type works perfectly. Specifically this this status_id relation which has the primary key of string doesn’t work. :cry:

What’s in %{@singular => body} when the error happens?

For some reason postgrex expects json from you, and I don’t see why anywhere in your code. So I guessed that the problem was in your macro.

@singular which in jobs controller case has the value of job wraps the job parameters

What’s in body? What is the returned from @model.changeset?

Just the jobs section please. here is the changeset example
changeset = Qber.V1.JobModel |> struct |> Qber.V1.JobModel.changeset(%{“status_id” => “confirmed”, “title” => “test”})

ahh, I made this simple. I just tried query in iex

changeset = Qber.V1.JobModel |> struct |> Qber.V1.JobModel.changeset(%{"status_id" => "confirmed", "title" => "test", "service_id" => 1})
Qber.Repo.insert changeset

same error

Can you print the contents of changeset? Anything unusual in there?

I’ve mostly copied the code from this thread and it works fine … The problem must be somewhere else.

Thanks. Let me try that :slight_smile:

Thanks @idi527 Finally got it working. Code was absolutely good. The problem was in triggers, thats why the weird postgress error. :smiley: