Ecto - non-ID foreign keys?

Questions / Help
1

What is the simplest and most idiomatic way of handling a situation in which a table has a foreign key to a field that is not the primary key of the originating table (I have working code, but it feels ugly). Here is a toy example:

schema "profile" do
  field :email, :string
  field :language, :string
  ... many others ...
end

schema "auth" do
  belongs_to :email, Profile, type: :string, foreign_key: :email, references: :email
  field :password_hash, :string
end

Given params of the type
%{"email" => "a@b.c", "language" => "ES", "password" => "tooshort", etc}

How would one write the changeset functions for Profile and Auth, so as to populate both the “profile” and “auth” table from the params, in a simple and idiomatic way? All my attempts at Repo.preload, cast_assoc, put_assoc etc fail… (I do have working code, but I had to make the schemas above more complex, and the whole thing is somewhat ugly.)

(They work fine if the foreign key is the usual fieldname_id refering back to the id of the first table, along the lines of:

schema "auth" do
  belongs_to :profile, Profile
  field :password_hash, :string
end

and getting the email directly from the profile. But the tables are a given, so I’m not at a liberty to do that.)

thanks!
kk

2 Likes
2

Can you use Ecto.Multi maybe?

3

According to the docs, Ecto.Multi is “a data structure for grouping multiple Repo operations” – could you elaborate on how that would help with one particular operation that doesn’t seem to work as expected (ie. an Ecto association which doesn’t target an id of the foreign table, like :email of the above example)? Thanks!

4

I thought it could help with this. You would use multi to populate both tables from a single set of params.

5

The issue is not about populating both tables from a single set of params, that’s easy, in fact the original post mentioned:

and:

The issue is that the Ecto mechanisms around associations seem to stop working when the assoc doesn’t refer back to the primary key of the foreign table, or, more likely, something about my usage and understanding of these Ecto assoc doesn’t transfer to associations that don’t refer to the primary key of the foreign table.

6

Maybe this will help:

Profile:

  schema "profiles" do
    field :email, :string
    field :language, :string
    has_one :auth, Test.Auth, [foreign_key: :email, references: :email]
    timestamps()
  end

Auth:

  schema "auths" do
    belongs_to :profile, Test.Profile, [foreign_key: :email, references: :email, type: :string]
    field :password_hash, :string
    timestamps()
  end

Changeset:

def changeset(profile, params) do
  profile
  |> cast(params, @allowed_params)
  |> validate_required(@required_params)
  |> cast_assoc(:auth, required: true)
end

Usage example:

changeset(%Test.Profile{}, %{email: "aa@bgbb.com", language: "en", auth: %{email: "aa@bgbb.com", password_hash: "asdf"}})
|> Test.Repo.insert!()

See this blog post.

2 Likes
7

Thanks Michal – this is to the point and helpful!

(Though also essentially similar to what I had, once the real table is factored in, etc. except I thought you must preload the assocation before cast_assoc / put_assoc)

I am concluding from all this that the sql table is just badly setup and as such requires slightly hacky Ecto solutions.

kk

8

I don’t think it’s badly set up, I’m in the non-ID keys camp myself - I strongly believe we should be using all of the relational database’s features to the fullest, with proper data-driven designs, composite keys and normalized structure. I think Ecto’s defaults are bad and we should all feel bad, but hey - the focus is productivity with flexibility, tables with simple IDs are way easier to work with in the short term. And you can always go back to Ecto.Query or even raw SQL when needed.

1 Like