How to set references in Rails when using custom primary keys-CodePudding

I'm trying to save data fetched from Sellix API into the db in my Rails application.

Basically, there are 4 models: products, coupons, orders, and feedback.

Sellix has its own unique id on every object called "uniqid" so I decided to use it as the primary key in my models as well.

For some models, I want to save references for other tables. For example, I want to have a coupon as a reference for orders to find out which coupon has been used when placing that order.

This is how two schemas are now:

  create_table "coupons", id: false, force: :cascade do |t|
    t.string "uniqid", null: false
    t.string "code"
    t.decimal "discount"
    t.integer "used"
    t.datetime "expire_at"
    t.integer "created_at"
    t.integer "updated_at"
    t.integer "max_uses"
    t.index ["uniqid"], name: "index_coupons_on_uniqid", unique: true
  end

create_table "orders", id: false, force: :cascade do |t|
    t.string "uniqid", null: false
    t.string "order_type"
    t.decimal "total"
    t.decimal "crypto_exchange_rate"
    t.string "customer_email"
    t.string "gateway"
    t.decimal "crypto_amount"
    t.decimal "crypto_received"
    t.string "country"
    t.decimal "discount"
    t.integer "created_at"
    t.integer "updated_at"
    t.string "coupon_uniqid"
    t.index ["uniqid"], name: "index_orders_on_uniqid", unique: true
  end

The coupon_uniqid on orders table is the reference to the relevant coupon.

The order object on Sellix API already has that reference so currently I can save it this way.

But when I display all orders, I have to use Coupon.find_by(uniqid: order.coupon_uniqid) and it always iterate through every coupon record in the local db to find it as below.

CACHE Coupon Load (0.0ms) SELECT "coupons".* FROM "coupons" WHERE "coupons"."uniqid" = $1 LIMIT $2 [["uniqid", "62e95dea17de385"], ["LIMIT", 1]]

I can get rid of that if I can keep the coupon reference instead of the uniqid.

That's basically what I want to figure out.

CodePudding user response：

YAGNI. A better approach that you should consider is to just have your own primary key and treat the uniqid as a secondary identifier to be used when looking up records based on their external id.

That way everything just works with minimal configuration.

If you really want to break the conventions you can configure the type and name of the primary key when creating the table:

create_table :orders, id: :string, primary_key: :uniqid do |t|
  # ...
end

class Order < ApplicationRecord
  self.primary_key = :uniqid
end

Since this column won't automatically generate primary keys you'll need to deal with that in all your tests as well.

You then have to provide extra configuration when creating foreign key columns so that they are the same type and point to the right column on the other table:

class AddOrderIdToCoupons < ActiveRecord::Migration[7.0]
  def change
    add_reference :coupons, :order, 
      type: :string, # default is bigint
      null: false, 
      foreign_key: { primary_key: "uniqid" }
  end
end

And you also need to add configuration to all your assocations:

class Coupon < Application
  belongs_to :order, primary_key: "uniqid"
end

class Order < Application
  has_many :coupons, primary_key: "uniqid"
end