Why does my ecto refer a column which does not exist?

I wrote a test code for getting data from postgresql.

defmodule DbServerWeb.UserSchemaTest do
  use ExUnit.Case, async: true
  use DbServer.DataCase

  alias DbServer.Users
  alias DbServer.Schema.User

  describe "user schema test." do
    @insert_params %{
      user_id: "test_id",
      user_name: "test_name",
      user_email: "email@gmail.com",
      user_password: "Password123?",
      user_gender: 0,
      user_bio: "Howdy?",
      user_birthday: DateTime.utc_now()
    }

    test "crud test." do
      assert {_, struct} = Users.create_user(@insert_params)
      assert %User{} = user = Users.get_user(struct.user_id)
    end
  end
end
defmodule DbServer.Users do
  @moduledoc """
    The users context.
  """
  import Ecto.Query, warn: false

  alias DbServer.Repo
  alias DbServer.Schema.User

  def create_user(user \\ %{}) do
    %User{}
    |> User.changeset(user)
    |> Repo.insert()
  end

  def get_user(id \\ :empty) do
    Repo.get!(User, id)
  end
end
defmodule DbServer.Repo.Migrations.Users do
  use Ecto.Migration

  def change do
    create table(:users, primary_key: false) do
      add :user_id, :string, primary_key: true
      add :user_name, :string
      add :user_email, :string
      add :user_password, :string
      add :user_gender, :integer
      add :user_bio, :string
      add :user_birthday, :utc_datetime
      add :user_hosting_experience, :integer
      add :following_games_id, references(:games)

      timestamps()
    end
  end
end
defmodule DbServer.Schema.Game do
  use Ecto.Schema
  import Ecto.Changeset

  #@primary_key {:game_id, :id, autogenerate: true}

  schema "games" do
    field :game_name, :string

    has_many :users, DbServer.Schema.User
    has_one :tournaments_game, DbServer.Schema.Tournament

    timestamps()
  end

  def changeset(game, params \\ :empty) do
    game
    |> cast(params, [:game_name])
    |> validate_required(:game_name)
    |> unique_constraint(:game_name, message: "The game already exists.")
  end
end

But my terminal says

** (Postgrex.Error) ERROR 42703 (undefined_column) column u0.following_games_id does not exist
     
         query: SELECT u0."user_id", u0."user_name", u0."user_email", u0."user_password", u0."user_gender", u0."user_bio", u0."user_birthday", u0."user_hosting_experience", u0."following_games_id", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."user_id" = $1)
     
         hint: Perhaps you meant to reference the column "u0.following_games".

Why my ecto refer following_games_id not following_games?

Can you also post your User module? It’s the relevant piece here.

4 Likes

Oops, Sorry.

defmodule DbServer.Schema.User do
  use Ecto.Schema
  import Ecto.Changeset
  alias Comeonin.Bcrypt

  @primary_key {:user_id, :string, []}

  schema "users" do
    field :user_name, :string
    field :user_email, :string
    field :user_password, :string
    field :user_gender, :integer
    field :user_bio, :string
    field :user_birthday, :utc_datetime
    field :user_hosting_experience, :integer, default: 0

    belongs_to :following_games, DbServer.Schema.Game

    has_one :tournament, DbServer.Schema.Tournament

    timestamps()
  end

  @user_name_regex ~r"^[a-z0-9_\-\.]+$"
  @email_regex ~r/@/

  @doc false
  def changeset(user, params \\ :empty) do
    user
    |> cast(params, [:user_id, :user_name, :user_email, :user_password, :user_gender, :user_bio, :user_birthday, :user_hosting_experience])
    |> validate_required([:user_id, :user_name, :user_email, :user_password, :user_gender, :user_bio, :user_birthday, :user_hosting_experience])
    |> unique_constraint(:user_id, message: "The id has been already taken.")
    |> validate_length(:user_name, min: 3)
    |> validate_format(:user_name, @user_name_regex)
    |> unique_constraint(:user_email, message: "The email already exists.")
    |> validate_format(:user_email, @email_regex, message: "Invalid format.")
    |> validate_length(:user_password, min: 8, max: 20)
    |> validate_format(:user_password, ~r/[A-Z]+/, message: "Password must contain an upper-case letter.")
    |> validate_format(:user_password, ~r/[a-z]+/, message: "Password must contain a lower-case letter.")
    |> validate_format(:user_password, ~r/[0-9]+/, message: "Password must contain a number.")
    |> validate_format(:user_password, ~r/[#\!\?&@\$%^&*\(\)]+/, message: "Password must contain a symbol.")
    |> validate_confirmation(:user_password, message: "Does not match password.")
    |> put_pass_hash()
    |> validate_length(:user_bio, max: 125)
  end

  defp put_pass_hash(%Ecto.Changeset{valid?: true, changes: %{password: password}} = changeset) do
    change(changeset, user_password: Bcrypt.hashpwsalt(password))
  end
  defp put_pass_hash(changeset), do: changeset
end

Thank you!

This message from Postgrex is the key: it’s suggesting a column named following_games. Was there a previous version of DbServer.Repo.Migrations.Users that used that column name instead of the current one? You may need to roll the migration back and then forward again to get the correct schema.

Zooming out, the name of the association here is odd; belongs_to can only point to one record, so naming one with a plural form like following_games is usually a sign the model isn’t correct. Consider many_to_many or has_many ... through: if a user can follow many games.

4 Likes

Woah! It worked with these codes! Thanks! <3

defmodule DbServer.Schema.Game do
  use Ecto.Schema
  import Ecto.Changeset

  schema "games" do
    field :game_name, :string

    many_to_many :users, DbServer.Schema.User, join_through: "users"
    has_one :tournaments_game, DbServer.Schema.Tournament

    timestamps()
  end

  def changeset(game, params \\ :empty) do
    game
    |> cast(params, [:game_name])
    |> validate_required(:game_name)
    |> unique_constraint(:game_name, message: "The game already exists.")
  end
end
defmodule DbServer.Schema.User do
  use Ecto.Schema
  import Ecto.Changeset
  alias Comeonin.Bcrypt

  @primary_key {:user_id, :string, []}

  schema "users" do
    field :user_name, :string
    field :user_email, :string
    field :user_password, :string
    field :user_gender, :integer
    field :user_bio, :string
    field :user_birthday, :utc_datetime
    field :user_hosting_experience, :integer, default: 0

    #belongs_to :following_games, DbServer.Schema.Game

    has_one :tournament, DbServer.Schema.Tournament

    timestamps()
  end

  @user_name_regex ~r"^[a-z0-9_\-\.]+$"
  @email_regex ~r/@/

  @doc false
  def changeset(user, params \\ :empty) do
    user
    |> cast(params, [:user_id, :user_name, :user_email, :user_password, :user_gender, :user_bio, :user_birthday, :user_hosting_experience])
    |> validate_required([:user_id, :user_name, :user_email, :user_password, :user_gender, :user_bio, :user_birthday, :user_hosting_experience])
    |> unique_constraint(:user_id, message: "The id has been already taken.")
    |> validate_length(:user_name, min: 3)
    |> validate_format(:user_name, @user_name_regex)
    |> unique_constraint(:user_email, message: "The email already exists.")
    |> validate_format(:user_email, @email_regex, message: "Invalid format.")
    |> validate_length(:user_password, min: 8, max: 20)
    |> validate_format(:user_password, ~r/[A-Z]+/, message: "Password must contain an upper-case letter.")
    |> validate_format(:user_password, ~r/[a-z]+/, message: "Password must contain a lower-case letter.")
    |> validate_format(:user_password, ~r/[0-9]+/, message: "Password must contain a number.")
    |> validate_format(:user_password, ~r/[#\!\?&@\$%^&*\(\)]+/, message: "Password must contain a symbol.")
    |> validate_confirmation(:user_password, message: "Does not match password.")
    |> put_pass_hash()
    |> validate_length(:user_bio, max: 125)
  end

  defp put_pass_hash(%Ecto.Changeset{valid?: true, changes: %{password: password}} = changeset) do
    change(changeset, user_password: Bcrypt.hashpwsalt(password))
  end
  defp put_pass_hash(changeset), do: changeset
end