Help needed !

[hutou]

I'm beginning to discover crecto and am having some difficulty using it. In particular, I stumble on the following problem: how to insert an instance of each data model, in relation has_one <-> belongs_to, using Multi to ensure data consistency in the database? Here is the Crystal code of a simplified example :

require "sqlite3"
require "crecto"

module MyRepo
  extend Crecto::Repo

  config do |conf|
    conf.adapter = Crecto::Adapters::SQLite3
    conf.hostname = "localhost"
    conf.database = "test.db"
  end
end

Multi = Crecto::Multi

class Operation < Crecto::Model
  set_created_at_field nil
  set_updated_at_field nil
  schema "operation" do
    field :quantity, Int32
    field :price, Float64
    has_one :score, Score
  end
end

class Score < Crecto::Model
  schema "score" do
    set_created_at_field nil
    set_updated_at_field nil
    field :points, Int32
    belongs_to :operation, Operation, foreign_key: operation_id # table score has field operation_id as foreign key
  end
end

operation = Operation.new
operation.quantity = 10
operation.price = 3.14

score = Score.new
score.points = 33
score.operation = operation

multi = Multi.new
multi.insert(operation)
multi.insert(score)
MyRepo.transaction(multi)
puts multi.errors

And the sql code to create the database :

.open "test.db";

drop table if exists "operation";
create table "operation" (
  "id" integer,
  "quantity" integer,
  "price" real,
  primary key ("id")
);

drop table if exists "score";
create table "score" (
  "id" integer,
  "points" integer,
  "operation_id" integer,
  primary key ("id") ,
  foreign key ("operation_id") references "operation" ("id")
);

Using this code, I get no error and both model instances are recorded in the database, but, in the score table, field operation_id is NULL !

I'd really appreciate your help on this one, and I would be delighted if I could have some reading tips on crecto, or some good code examples to study. Thanks

[fridgerator]

@hutou maybe try setting the foreign_key option on the has_one within Operation instead?

https://www.crecto.com/crecto-model/associations#has-many-and-belongs-to

[hutou]

Hi, Thanks for your quick answer, but this doesn't work either. I began studying the crecto code, and so far, I think that :

1. Specifying foreign_key is only necessary if the foreign_key does not follow the naming convention : tablename_id
2. Inserting two (or more) instances of data models having a relationship inside a Multi transaction does not seem possible, as a valid id is needed (which is nil until database operation is done). That also could explain why, in your documentation, there is always a Repo.get before setting associations : am I right ?

So, I see no solution other than not using a transaction in this case. I got my code working using

  operation = Operation.new
  operation.quantity = 10
  operation.price = 3.14
  cs = MyRepo.insert!(operation)
  operation = MyRepo.get!(Operation, cs.instance.id)

  score = Score.new
  score.points = 33
  score.operation = operation

  cs = MyRepo.insert!(score)
  puts cs.errors

without specifying a foreign_key in schema. Thank you in any case for this library that, this small problem aside, I find particularly easy to use.