bigbassroller

bigbassroller

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!

Showing Posts 1 to 3

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.

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?)

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.

— All posts loaded —

Where Next? Top

Trending in Questions Top

axelson
One of my LiveView view’s has a very heavy javascript dependency so I want to avoid loading it for every page on the site, while still us...
New
Hellowman
Hello everyone! I am new to coding with Elixir/Erlang. And i am looking for some utilities for fuzzing testing like AFL++. Does anyone ...
New
yarrichar
I’m very new to Elixir, so sorry if this is a silly question: I am wondering what happens to the mailbox of a process when the machine i...
New
pragdave
How do I tell Hologram not to escape HTML when substituting into templates? {Textile.render(@product.long_description)} escapes the HTM...
New
mandar1045
I load tested a plain Phoenix chat app backed by Postgres with 100 million messages in it, everything on default settings, to see what br...
New
Fl4m3Ph03n1x
Background When trying to execute mix release on a Windows 11 machine for a Phoenix project I get the following error: * assembling mark...
New
rlipscombe
I’d like to create a file with 600 permissions (rw-------), but there’s a race condition if I create it and then change the permissions. ...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
261 13183 112
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
kip
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews