Ecto : how to do when the primary key of a table is the id of an association

Question

I'm working on a REST API for freeradius-mysql (so I don't manage the database...) Freeradius has this weird schema:

the "group" table:

schema "radusergroup" do
  field :username, :string, primary_key: true
  field :priority, :integer
  field :groupname, :string
end

the "user" table:

schema "userinfo" do
 field :username, :string 
 field :firstname, :string
 field :lastname, :string
 field :email, :string
 .
 .
 .
 field :creationdate, :datetime
 field :creationby, :string
 field :updatedate, :datetime
 field :updateby, :string
 field :username, :string
 has_one :group, Rascal.Group, references: :username,
   foreign_key: :username, on_delete: :delete_all
end

My issue is that I would like to do:

schema "radusergroup" do
  belongs_to :user, Rascal.User, foreign_key: :username, references: :username, type: :string, primary_key: true
  field :priority, :integer
  field :groupname, :string
end

But Ecto doesn't allow it. any idea to do it easily?

Thanks!

Answer 1

I have tried to implement the structure you need.

Migrations

User:

defmodule ExampleApp.Repo.Migrations.CreateUser do
  use Ecto.Migration

  def change do
    create table(:userinfo, primary_key: false) do
      add :username, :string, primary_key: true # note the primary_key

      timestamps
    end
    create unique_index(:userinfo, [:username])


  end
end

Group:

defmodule ExampleApp.Repo.Migrations.CreateGroup do
  use Ecto.Migration

  def change do
    create table(:radusergroup) do

      add :username, references(:userinfo, on_delete: :nothing, column: :username, type: :string)

      add :priority, :integer
      add :groupname, :string

      timestamps
    end
    create index(:radusergroup, [:username])

  end
end

Models

User:

defmodule ExampleApp.User do
  use ExampleApp.Web, :model

  @primary_key {:username, :string, autogenerate: false}
  schema "userinfo" do
    has_one :group, ExampleApp.Group, foreign_key: :username, on_delete: :delete_all

    timestamps
  end

  @required_fields ~w()
  @optional_fields ~w()

  def changeset(model, params \\ :empty) do
    model
    |> cast(params, @required_fields, @optional_fields)
  end
end

Group:

defmodule ExampleApp.Group do
  use ExampleApp.Web, :model

  schema "radusergroup" do
    belongs_to :username, ExampleApp.Username, references: :username, type: :string

    field :priority, :integer
    field :groupname, :string

    timestamps
  end

  @required_fields ~w()
  @optional_fields ~w()

  def changeset(model, params \\ :empty) do
    model
    |> cast(params, @required_fields, @optional_fields)
  end
end

Does it suite your needs?