Belongs_to assoc changeset not being inserted into database

Questions / Help
, , ,
1

Migrations

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

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

JobStatusModel

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

JobModel

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

Changeset of Job

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

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**
and

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: Phoenix.Router.call/1

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

2

What’s common?

3

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

4

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.

5 
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)
			end
		end
6

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:

7

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.

8

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

9

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

12

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

13

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

14

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

15

https://github.com/idi-ot/jobs

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

16

Thanks. Let me try that :slight_smile:

17

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