Questions & Help>Questions

Help with Using String Foreign Keys

/phoenix#ecto#troubleshooting#ecto-migration
bigbassroller

bigbassroller

Help with Using String Foreign Keys

I’ve been trying to get foreign_keys to work with string id’s for days and still have not been able to figure it out.

I am stuck with this error:

iex(3)> project_manager = Repo.all(from p in ProjectManager, preload: [:user])
[debug] QUERY OK source="project_managers" db=0.3ms
SELECT p0."id", p0."wf_user_id", p0."inserted_at", p0."updated_at" FROM "project_managers" AS p0 []
** (Ecto.Query.CastError) deps/ecto/lib/ecto/association.ex:901: value `["5b89e3d300abc77aa4885e819fe3713d", "5b93028601bd041b925dc2067422be82", "5ba94a2a00854529705f809ebec755b9"]` in `where` cannot be cast to type {:in, :id} in query:

from u0 in ExampleApp.Users.User,
  where: u0.id in ^["5b89e3d300abc77aa4885e819fe3713d", "5b93028601bd041b925dc2067422be82", "5ba94a2a00854529705f809ebec755b9"],
  select: {u0.id, u0}

    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1440: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/repo/queryable.ex:161: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2

I distilled to minimum reproduction of the issue, documented the steps and put up on a repo.

Repo:
https://github.com/bigbassroller/Elixir-Phoenix-Foreign-Key-Example

Here are the steps to reproduce (taken from the README of the repo).

ExampleApp

First, make sure you have Elixir and Phoenix installed:

Also make sure you have PostgreSQL installed:

mix phx.new example_app

cd example_app

mix ecto.create

mix phx.server

Add User Schema

mix phx.gen.schema Users.User users wf_user_id:string:unique email:string:unique

Add Project Manager Schema

mix phx.gen.schema ObjectTypes.ProjectManager project_managers wf_user_id:references:users:unique

Add Belongs to User to Project Manager

lib/example_app/object_types/project_manager.ex

defmodule ExampleApp.ObjectTypes.ProjectManager do
  use Ecto.Schema
  import Ecto.Changeset
  alias ExampleApp.Users.User
  # use ExampleApp.Schema


  schema "project_managers" do
    belongs_to :user, User, foreign_key: :wf_user_id, type: :string, primary_key: true

    timestamps()
  end

  @doc false
  def changeset(project_manager, attrs) do
    project_manager
    |> cast(attrs, [])
    |> validate_required([])
    |> unique_constraint(:wf_user_id)
  end
end

Set schema primary keys and foreign key type

Also tried with this, but still no dice:(

lib/example_app/schema.ex

defmodule ExampleApp.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:wf_user_id, :string, autogenerate: false}
      @foreign_key_type :string
    end
  end
end

priv/repo/migrations/20191208072158_create_users.exs

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

  def change do
    create table(:users) do
      add :wf_user_id, :string, primary_key: true
      add :email, :string

      timestamps()
    end

    create unique_index(:users, [:wf_user_id])
    create unique_index(:users, [:email])
  end
end

priv/repo/migrations/20191208072217_create_project_managers.exs

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

  def change do
    create table(:project_managers) do
      add :wf_user_id, references(:users, column: :wf_user_id, type: :string, on_delete: :delete_all), null: false, primary_key: true

      timestamps()
    end

    create unique_index(:project_managers, [:wf_user_id])
  end
end

Customize iex

.iex.exs

import_if_available Ecto.Query

alias ExampleApp.{
    Repo,
    Users.User,
    ObjectTypes,
    ObjectTypes.Project,
    ObjectTypes.ProjectManager
}

Seed User table with Mock Data from JSON response

priv/repo/user-seeds.exs

# Script for populating the database. You can run it as:
#
#     mix run priv/repo/user-seeds.exs

alias ExampleApp.Repo
alias ExampleApp.Users.User

# ID is from legacy system
data = [
  %{
    "ID" => "5ba94a2a00854529705f809ebec755b9",
    "emailAddr" => "user1@test.com",
  },
  %{
    "ID" => "5b93028601bd041b925dc2067422be82",
    "emailAddr" => "user2@test.com",
  },
  %{
    "ID" => "5b89e3d300abc77aa4885e819fe3713d",
    "emailAddr" => "user3@test.com",
  }
]

Enum.each data, fn(user) ->

  # Map Data here
  wf_user_id = Map.get(user, "ID")
  email_addr = Map.get(user, "emailAddr")

  # Insert data into database
  Repo.insert! %User{
    wf_user_id: wf_user_id,
    email: email_addr
  }
end

mix run priv/repo/user-seeds.exs

Seed Project Manager table with Mock Data from JSON response

priv/repo/project-manager-seeds.exs

# Script for populating the database. You can run it as:
#
#     mix run priv/repo/project-manager-seeds.exs

alias ExampleApp.Repo
alias ExampleApp.ObjectTypes.{ProjectManager}

data = [
  %{
    "ID" => "5ba94b28008708718d6e1b4d36a79770",
    "name" => "Project 1",
    "ownerID" => "5ba94a2a00854529705f809ebec755b9"
  },
  %{
    "ID" => "5bae225c02b878793ca699dc8a4b9b9a",
    "name" => "Project 2",
    "ownerID" => "5b93028601bd041b925dc2067422be82"
  },
  %{
    "ID" => "5bc8ae5a00251a1cb4ee27c4860e29b1",
    "name" => "Project 3",
    "ownerID" => "5b89e3d300abc77aa4885e819fe3713d"
  }
]

Enum.each data, fn(project_manager) ->

  wf_user_id = Map.get(project_manager, "ownerID")

  # Insert data into database
  Repo.insert! %ProjectManager{
    wf_user_id: wf_user_id
  }
end

mix run priv/repo/project-manager-seeds.exs

Do a reset and reseed:

mix ecto.reset && mix run priv/repo/user-seeds.exs \
&& mix run priv/repo/project-manager-seeds.exs

Test it out:

iex -S mix

users = Repo.all(from u in User)

Outputs:

[debug] QUERY OK source="users" db=5.9ms decode=1.0ms queue=1.3ms
SELECT u0."id", u0."email", u0."wf_user_id", u0."inserted_at", u0."updated_at" FROM "users" AS u0 []
[
  %ExampleApp.Users.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    email: "user1@test.com",
    id: 1,
    inserted_at: ~N[2019-12-08 08:00:10],
    updated_at: ~N[2019-12-08 08:00:10],
    wf_user_id: "5ba94a2a00854529705f809ebec755b9"
  },
  %ExampleApp.Users.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    email: "user2@test.com",
    id: 2,
    inserted_at: ~N[2019-12-08 08:00:10],
    updated_at: ~N[2019-12-08 08:00:10],
    wf_user_id: "5b93028601bd041b925dc2067422be82"
  },
  %ExampleApp.Users.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    email: "user3@test.com",
    id: 3,
    inserted_at: ~N[2019-12-08 08:00:10],
    updated_at: ~N[2019-12-08 08:00:10],
    wf_user_id: "5b89e3d300abc77aa4885e819fe3713d"
  }
]

project_manager = Repo.all(from p in ProjectManager)

Outputs:

[debug] QUERY OK source="project_managers" db=1.4ms queue=1.4ms
SELECT p0."id", p0."wf_user_id", p0."inserted_at", p0."updated_at" FROM "project_managers" AS p0 []
[
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 1,
    inserted_at: ~N[2019-12-08 08:00:11],
    updated_at: ~N[2019-12-08 08:00:11],
    user: #Ecto.Association.NotLoaded<association :user is not loaded>,
    wf_user_id: "5ba94a2a00854529705f809ebec755b9"
  },
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 2,
    inserted_at: ~N[2019-12-08 08:00:11],
    updated_at: ~N[2019-12-08 08:00:11],
    user: #Ecto.Association.NotLoaded<association :user is not loaded>,
    wf_user_id: "5b93028601bd041b925dc2067422be82"
  },
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 3,
    inserted_at: ~N[2019-12-08 08:00:11],
    updated_at: ~N[2019-12-08 08:00:11],
    user: #Ecto.Association.NotLoaded<association :user is not loaded>,
    wf_user_id: "5b89e3d300abc77aa4885e819fe3713d"
  }
]

project_manager = Repo.all(from p in ProjectManager, preload: [:user])

Roh oh! :scream: What does {:in, :id} mean?

iex(3)> project_manager = Repo.all(from p in ProjectManager, preload: [:user])
[debug] QUERY OK source="project_managers" db=0.3ms
SELECT p0."id", p0."wf_user_id", p0."inserted_at", p0."updated_at" FROM "project_managers" AS p0 []
** (Ecto.Query.CastError) deps/ecto/lib/ecto/association.ex:901: value `["5b89e3d300abc77aa4885e819fe3713d", "5b93028601bd041b925dc2067422be82", "5ba94a2a00854529705f809ebec755b9"]` in `where` cannot be cast to type {:in, :id} in query:

from u0 in ExampleApp.Users.User,
  where: u0.id in ^["5b89e3d300abc77aa4885e819fe3713d", "5b93028601bd041b925dc2067422be82", "5ba94a2a00854529705f809ebec755b9"],
  select: {u0.id, u0}

    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1440: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/repo/queryable.ex:161: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2

Expected output is something like this:

[
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 1,
    inserted_at: ~N[2019-12-05 17:20:11],
    projects: #Ecto.Association.NotLoaded<association :projects is not loaded>,
    updated_at: ~N[2019-12-05 17:20:11],
    user: %ExampleApp.Users.User{
      __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
      email: "user1@test.com",
      id: 1,
      inserted_at: ~N[2019-12-05 17:20:10],
      updated_at: ~N[2019-12-05 17:20:10],
      wf_user_id: "5ba94a2a00854529705f809ebec755b9"
    },
    wf_user_id: "5ba94a2a00854529705f809ebec755b9"
  },
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 2,
    inserted_at: ~N[2019-12-05 17:20:11],
    projects: #Ecto.Association.NotLoaded<association :projects is not loaded>,
    updated_at: ~N[2019-12-05 17:20:11],
    user: %ExampleApp.Users.User{
      __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
      email: "user2@test.com",
      id: 2,
      inserted_at: ~N[2019-12-05 17:20:10],
      updated_at: ~N[2019-12-05 17:20:10],
      wf_user_id: "5b93028601bd041b925dc2067422be82"
    },
    wf_user_id: "5ba94a2a00854529705f809ebec755b9"
  },
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 3,
    inserted_at: ~N[2019-12-05 17:20:11],
    projects: #Ecto.Association.NotLoaded<association :projects is not loaded>,
    updated_at: ~N[2019-12-05 17:20:11],
    user: %ExampleApp.Users.User{
      __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
      email: "user3@test.com",
      id: 3,
      inserted_at: ~N[2019-12-05 17:20:10],
      updated_at: ~N[2019-12-05 17:20:10],
      wf_user_id: "5b89e3d300abc77aa4885e819fe3713d"
    },
    wf_user_id: "5b89e3d300abc77aa4885e819fe3713d"
  }
]

This works though :person_tipping_hand:

q = from ProjectManager, where: [wf_user_id: "5b93028601bd041b925dc2067422be82"]
Repo.all(q)

iex(1)> q = from ProjectManager, where: [wf_user_id: "5b93028601bd041b925dc2067422be82"]
#Ecto.Query<from p0 in ExampleApp.ObjectTypes.ProjectManager,
 where: p0.wf_user_id == "5b93028601bd041b925dc2067422be82">
iex(2)> Repo.all(q)
[debug] QUERY OK source="project_managers" db=6.1ms decode=1.0ms queue=2.9ms
SELECT p0."id", p0."wf_user_id", p0."inserted_at", p0."updated_at" FROM "project_managers" AS p0 WHERE (p0."wf_user_id" = '5b93028601bd041b925dc2067422be82') []
[
  %ExampleApp.ObjectTypes.ProjectManager{
    __meta__: #Ecto.Schema.Metadata<:loaded, "project_managers">,
    id: 2,
    inserted_at: ~N[2019-12-08 08:00:11],
    updated_at: ~N[2019-12-08 08:00:11],
    user: #Ecto.Association.NotLoaded<association :user is not loaded>,
    wf_user_id: "5b93028601bd041b925dc2067422be82"
  }
]

Users Table Output

                                         Table "public.users"
   Column    |              Type              | Collation | Nullable |              Default              
-------------+--------------------------------+-----------+----------+-----------------------------------
 id          | bigint                         |           | not null | nextval('users_id_seq'::regclass)
 wf_user_id  | character varying(255)         |           | not null | 
 email       | character varying(255)         |           |          | 
 inserted_at | timestamp(0) without time zone |           | not null | 
 updated_at  | timestamp(0) without time zone |           | not null | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id, wf_user_id)
    "users_email_index" UNIQUE, btree (email)
    "users_wf_user_id_index" UNIQUE, btree (wf_user_id)
Referenced by:
    TABLE "project_managers" CONSTRAINT "project_managers_wf_user_id_fkey" FOREIGN KEY (wf_user_id) REFERENCES users(wf_user_id) ON DELETE CASCADE

 id |            wf_user_id            |     email      |     inserted_at     |     updated_at      
----+----------------------------------+----------------+---------------------+---------------------
  1 | 5ba94a2a00854529705f809ebec755b9 | user1@test.com | 2019-12-08 08:00:10 | 2019-12-08 08:00:10
  2 | 5b93028601bd041b925dc2067422be82 | user2@test.com | 2019-12-08 08:00:10 | 2019-12-08 08:00:10
  3 | 5b89e3d300abc77aa4885e819fe3713d | user3@test.com | 2019-12-08 08:00:10 | 2019-12-08 08:00:10
(3 rows)

Project Managers Table Output

                                          Table "public.project_managers"
   Column    |              Type              | Collation | Nullable |                   Default                    
-------------+--------------------------------+-----------+----------+----------------------------------------------
 id          | bigint                         |           | not null | nextval('project_managers_id_seq'::regclass)
 wf_user_id  | character varying(255)         |           | not null | 
 inserted_at | timestamp(0) without time zone |           | not null | 
 updated_at  | timestamp(0) without time zone |           | not null | 
Indexes:
    "project_managers_pkey" PRIMARY KEY, btree (id, wf_user_id)
    "project_managers_wf_user_id_index" UNIQUE, btree (wf_user_id)
Foreign-key constraints:
    "project_managers_wf_user_id_fkey" FOREIGN KEY (wf_user_id) REFERENCES users(wf_user_id) ON DELETE CASCADE

 id |            wf_user_id            |     inserted_at     |     updated_at      
----+----------------------------------+---------------------+---------------------
  1 | 5ba94a2a00854529705f809ebec755b9 | 2019-12-08 08:00:11 | 2019-12-08 08:00:11
  2 | 5b93028601bd041b925dc2067422be82 | 2019-12-08 08:00:11 | 2019-12-08 08:00:11
  3 | 5b89e3d300abc77aa4885e819fe3713d | 2019-12-08 08:00:11 | 2019-12-08 08:00:11
(3 rows)

Any idea what I am missing?

Thanks in advance for any help!

3 comments
/phoenix#ecto#troubleshooting#ecto-migration
3153 3

First 3 of 3 Posts Switch mode

idi527

idi527

:waving_hand:

You probably need to specify the key to use in the user schema for associations, which is :wf_user_id since by default ecto would look for :id. And in ecto migrations you might want to specify primary_key: false when creating the users table, so that ecto doesn’t add the :id column.

0
Post #1
bigbassroller

bigbassroller OP

Thanks! It’s finally working :grinning: The key parts that I was missing was the |> unique_constraint(:wf_user_id, name: "project_managers_pkey") which I came across during other renditions, but seemed to have slipped by in later ones, and it’s not clear that something like that has to be added in the ecto docs (I skimmed a lot). Another part that I was missing was the references: :wf_user_id in the belongs_to, which I see now in the link you shared, but missed while banging my head.

Thanks @syfgkjasdkn and @idi527! (or are you the same person?)

2
Post #2
idi527

idi527

Is not necessary, I only added it because it was in your changesets before, but with an updated constraint name. The only necessary change was references: :wf_user_id.

1
Post #3
— All posts loaded —

Where Next?

View thread on forum (has 3 replies!)
phoenix
ecto
troubleshooting
ecto-migration
Home Questions & Help>Questions
/phoenix#ecto#troubleshooting#ecto-migration
3 3153 3
Last post
REPLY VIEW THREAD WITH 3 POSTS

Trending in Questions Top

jonnycharles
Questions & Help>Questions

Looking for a Prawn-Like PDF Generation Library in Elixir
I’m in search of an Elixir library that offers PDF generation capabilities similar to Ruby’s Prawn. While there have been discussions abo...
#learning-elixir
117 2851 30
New
spammy
Questions & Help>Questions

Simple/light way to self host microapps and services?
I’m looking to build a personal workflow to quickly deploy web applications written in elixir/phoenix, for local consumption (ie not on t...
#deployment
34 1088 21
New
silverdr
Questions & Help>Questions

Using Phoenix.LiveView.TagEngine as an EEx.Engine is deprecated!
Using Phoenix.LiveView.TagEngine as an EEx.Engine is deprecated! To compile HEEx, use Phoenix.LiveView.TagEngine.compile/2 instead. Sta...
/phoenix#liveview
3 821 6
New
dli
Questions & Help>Questions

How to integrate Hologram with existing LiveView app?
Before I dive in myself, did anyone successfully sprinkle Hologram into their existing LiveView app? Looking for hints regarding: Addi...
/hologram#liveview
11 709 6
New
bottlenecked
Questions & Help>Questions

How to do proper post-release steps
Hi all, I wanted to ask how the community is dealing with post-release steps. Today we have Ecto migrations, which make sure that the db...
/phoenix#deployment
13 516 9
New
michallepicki
Questions & Help>Questions

How to start Oban, but delay job processing?
I am using Oban and occasionally, shortly after a deployment, a handful of jobs can fail because of dependency on other parts of the syst...
/oban
6 490 3
New
rahultumpala
Questions & Help>Questions

Help with load testing a custom protocol over TCP
Hello, I have an Elixir backend that implements a custom protocol over TCP. I want to load test the backend and assess the performance o...
#tcp#gen_tcp#loadtesting
1 486 5
New

Other Trending Topics Top

JesseHerrick
News>Announcing

Dexter - A fast, full-featured Elixir LSP optimized for large codebases
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
#library#lsp#dexter
177 5170 56
New
jimsynz
News>Announcing

Beam Bots - Resilient Robotics on the BEAM
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
/beam-bots#library#robotics#bb
119 2241 27
New
Damirados
News>Announcing

Emerge & Solve - a GUI framework for Elixir
Hello everyone. After busy few months I am happy to announce v0.1.0 of Emerge &amp; Solve. They are GUI (Emerge) and State management (S...
/nerves#library#frameworks#gui#native#state
59 1888 22
New
ausimian
News>Announcing

Emily - A new MLX-based backend for Nx
Emily is an Elixir library that runs Nx computations on Apple’s MLX. Install it as the default Nx backend and Nx, defn, Axon, Nx.Serving,...
#library#libraries#emily
62 1895 19
New
type1fool
Chat & Discussions>Discussions

Elixir-lang.org redesign
I just stumbled on a newly redesigned elixir-lang.org. :tada: It looks like @Software_Mansion did the work, and I think it is generally a...
#marketing
43 1445 11
New
akoutmos
Learning Resources>Books

Programming Nerves (self-published)
@hugobarauna and I (Alex Koutmos) have been hard at work on writing a book on Nerves that takes you from simply blinking LEDs to building...
/nerves/livebook#book#liveview#learning
39 1137 18
New
Back to top

Latest Phoenix Threads

Phoenix Forum ❯

Questions & Help>Questions

Questions Questions ❯

Latest on Elixir Forum

Elixir Forum ❯

Sponsor Spotlight

Defacto

Bringing learning, work and compliance together since 1987.

Our Sponsors

Dashbit

Build Elixir applications with speed and confidence.

EEF

Supporting innovation across the BEAM ecosystem.

Coders51

We build reliable cloud platforms for business-critical systems.

AppSignal

Catch errors, track performance, monitor hosts and more.

Honeybadger

Error tracking, logs, uptime, and Just Enough APM. Built for developers.

Manning

Develop your skills with books, videos, and courses.

WyeWorks

Enabling companies to succeed by building software people love.

Potions

The deployment platform built for Elixir: PaaS ease, VPS control.

Software Mansion

The team behind Membrane, Popcorn, LiveDebugger. Available for hire.

Pragmatic Studio

Courses that'll move you from confusion to "Aha, now I get it!"

View all our sponsors ❯

Categories:

Sub Categories:

Forums

Popular Tags

Our Sponsors

Dashbit

Build Elixir applications with speed and confidence.

EEF

Supporting innovation across the BEAM ecosystem.

Coders51

We build reliable cloud platforms for business-critical systems.

AppSignal

Catch errors, track performance, monitor hosts and more.

Honeybadger

Error tracking, logs, uptime, and Just Enough APM. Built for developers.

Manning

Develop your skills with books, videos, and courses.

WyeWorks

Enabling companies to succeed by building software people love.

Potions

The deployment platform built for Elixir: PaaS ease, VPS control.

Software Mansion

The team behind Membrane, Popcorn, LiveDebugger. Available for hire.

Pragmatic Studio

Courses that'll move you from confusion to "Aha, now I get it!"

View all our sponsors ❯

We're in Beta

About us Mission Statement

Options

Show Best Posts Skip Thread Previews