Help with self referencing associations and custom pk/fk

I have these schemas:

User:

defmodule Fuschia.Entities.User do
  @moduledoc """
  User schema
  """

  use Fuschia.Schema

  import Ecto.Changeset

  alias Fuschia.Common.Formats
  alias Fuschia.Entities.{Contato, User}
  alias Fuschia.Types.{CapitalizedString, TrimmedString}

  @required_fields ~w(nome_completo cpf data_nascimento)a
  @optional_fields ~w(password confirmed last_seen nome_completo ativo perfil)a

  @valid_perfil ~w(pesquisador pescador admin avulso)

  @lower_pass_format ~r/[a-z]/
  @upper_pass_format ~r/[A-Z]/
  @special_pass_format ~r/[!?@#$%^&*_0-9]/

  @cpf_format Formats.cpf()

  @primary_key {:cpf, TrimmedString, autogenerate: false}
  schema "user" do
    field :password_hash, TrimmedString
    field :confirmed, :boolean, default: false
    field :data_nascimento, :date
    field :last_seen, :utc_datetime_usec
    field :perfil, TrimmedString, default: "avulso"
    field :nome_completo, CapitalizedString
    field :ativo, :boolean, default: true
    field :password, TrimmedString, virtual: true
    field :permissoes, :map, virtual: true
    field :is_admin, :boolean, virtual: true, default: false

    belongs_to :contato, Contato, on_replace: :update

    timestamps()
  end
end

Pesquisador:

defmodule Fuschia.Entities.Pesquisador do
  @moduledoc """
  Pesquisador Schema
  """

  use Fuschia.Schema
  import Ecto.Changeset

  alias Fuschia.Entities.{Campus, Pesquisador, User}
  alias Fuschia.Types.{CapitalizedString, TrimmedString}

  @required_fields ~w(minibiografia tipo_bolsa link_lattes campus_nome)a
  @optional_fields ~w(orientador_cpf)a

  @tipos_bolsa ~w(ic pesquisa voluntario)

  @primary_key {:usuario_cpf, TrimmedString, autogenerate: false}
  schema "pesquisador" do
    field :minibiografia, TrimmedString
    field :tipo_bolsa, TrimmedString
    field :link_lattes, TrimmedString
    field :orientador_cpf, TrimmedString

    has_many :orientandos, Pesquisador

    belongs_to :usuario, User,
      references: :cpf,
      define_field: false,
      foreign_key: :pesquisador_usuario_cpf,
      primary_key: true

    belongs_to :campus, Campus,
      foreign_key: :campus_nome,
      references: :nome,
      type: CapitalizedString

    has_one :orientador, Pesquisador,
      references: :usuario_cpf,
      foreign_key: :orientador_cpf

    timestamps()
  end
end

And my Pesquisadores context:

defmodule Fuschia.Context.Pesquisadores do
  @moduledoc """
  Public Fuschia Pesquisador API
  """

  import Ecto.Query

  alias Fuschia.Entities.Pesquisador
  alias Fuschia.Repo

  @spec list :: [%Pesquisador{}]
  def list do
    query()
    |> preload_all()
    |> Repo.all()
  end

  @spec one(String.t()) :: %Pesquisador{} | nil
  def one(cpf) do
    query()
    |> preload_all()
    |> Repo.get(cpf)
  end

  @spec list_by_orientador(String.t()) :: [%Pesquisador{}]
  def list_by_orientador(orientador_cpf) do
    query()
    |> where([p], p.orientador_cpf == ^orientador_cpf)
    |> order_by([p], desc: p.created_at)
    |> preload_all()
    |> Repo.one()
  end

  @spec create(map) :: {:ok, %Pesquisador{}} | {:error, %Ecto.Changeset{}}
  def create(attrs) do
    with {:ok, pesquisador} <-
           %Pesquisador{}
           |> Pesquisador.changeset(attrs)
           |> Repo.insert() do
      {:ok, preload_all(pesquisador)}
    end
  end

  @spec update(String.t(), map) :: {:ok, %Pesquisador{}} | {:error, %Ecto.Changeset{}}
  def update(usuario_cpf, attrs) do
    with %Pesquisador{} = pesquisador <- one(usuario_cpf),
         {:ok, updated} <-
           pesquisador
           |> Pesquisador.changeset(attrs)
           |> Repo.update() do
      {:ok, updated}
    end
  end

  @spec exists?(String.t()) :: boolean
  def exists?(usuario_cpf) do
    Pesquisador
    |> where([p], p.usuario_cpf == ^usuario_cpf)
    |> Repo.exists?()
  end

  @spec query :: %Ecto.Query{}
  def query do
    from p in Pesquisador,
      left_join: campus in assoc(p, :campus),
      left_join: orientador in assoc(p, :orientador),
      order_by: [desc: p.created_at]
  end

  @spec preload_all(%Ecto.Query{}) :: %Ecto.Query{}
  def preload_all(%Ecto.Query{} = query) do
    query
    |> Ecto.Query.preload(
      orientador: [usuario: :contato],
      orientandos: [usuario: :contato],
      usuario: :contato,
      campus: :cidade
    )
  end

  @spec preload_all(%Pesquisador{}) :: %Pesquisador{}
  def preload_all(%Pesquisador{} = pesquisador) do
    pesquisador
    |> Repo.preload(
      orientador: [usuario: :contato],
      orientandos: [usuario: :contato],
      usuario: :contato,
      campus: :cidade
    )
  end
end

However I’m having this error when trying to retrieve a pesquisador from db (eg. list/0, one/1):

22:51:09.498 [error] Task #PID<0.590.0> started from #PID<0.587.0> terminating
** (Ecto.QueryError) deps/ecto/lib/ecto/association.ex:765: field `pesquisador_usuario_cpf` in `where` does not exist in schema Fuschia.Entities.Pesquisador in query:

from p0 in Fuschia.Entities.Pesquisador,
  where: p0.pesquisador_usuario_cpf == ^"726.541.170-65",
  order_by: [asc: p0.pesquisador_usuario_cpf],
  select: {p0.pesquisador_usuario_cpf, p0}

    (elixir 1.12.1) lib/enum.ex:2356: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir 1.12.1) lib/enum.ex:1675: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir 1.12.1) lib/enum.ex:2356: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.6.2) lib/ecto/repo/queryable.ex:208: Ecto.Repo.Queryable.execute/4
    (ecto 3.6.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.6.2) lib/ecto/repo/preloader.ex:234: Ecto.Repo.Preloader.fetch_query/8
    (elixir 1.12.1) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.12.1) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.15) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

And when i try to preload the usuario: :contato assoc:

** (MatchError) no match of right hand side value: %Fuschia.Entities.Pesquisador{__meta__: #Ecto.Schema.Metadata<:loaded, "pesquisador">, campus: #Ecto.Association.NotLoaded<association :campus is not loaded>, campus_nome: "Campus 2", created_at: ~U[2021-09-0600:41:45.494467Z], link_lattes: "http://buscatextual.cnpq.br/buscatextual/:1", minibiografia: "Esta e minha minibiografia gerada: 1", orientador: #Ecto.Association.NotLoaded<association :orientador is not loaded>, orientador_cpf: nil, orientandos: #Ecto.Association.NotLoaded<association :orientandos is not loaded>, tipo_bolsa: "pesquisa", updated_at: ~U[2021-09-06 00:41:45.494467Z], usuario: #Ecto.Association.NotLoaded<association:usuario is not loaded>, usuario_cpf: "726.541.170-65"}
    (elixir 1.12.1) lib/enum.ex:2356: Enum."-reduce/3-lists^foldl/2-0-"/3
    (stdlib 3.15) maps.erl:410: :maps.fold_1/3
    (ecto 3.6.2) lib/ecto/repo/queryable.ex:230: Ecto.Repo.Queryable.execute/4
    (ecto 3.6.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.6.2) lib/ecto/repo/queryable.ex:151: Ecto.Repo.Queryable.one/3

This is an existing db so i can’t modify it. I also searched about anything close to my problem and I found kinda related problems but I ain’t able to solve mine.

There’s anything I can do to bypass these problems? I’m kinda confused with this self referecing queries

references:

Can you post the structure of the database? (migrations, or the SQL CREATE TABLE, etc)? Your Ecto schema believes that this field exists, but the table appears to disagree.

Of course!

This’s the user migration:

defmodule Fuschia.Repo.Migrations.CreateUsuariosAuthTables do
  use Ecto.Migration

  def change do
    create table(:user, primary_key: false) do
      add(:cpf, :citext, primary_key: true, null: false)
      add(:nome_completo, :string, null: false)
      add(:data_nascimento, :date, null: false)
      add(:perfil, :string, default: "avulso", null: false)
      add(:last_seen, :utc_datetime_usec)
      add(:ativo, :boolean, default: true, null: false)
      add(:password_hash, :string)
      add(:confirmed, :boolean, default: false)

      add(:contato_id, references(:contato, on_replace: :update), null: false)

      timestamps()
    end

    create(unique_index(:user, [:cpf]))
    create(unique_index(:user, [:nome_completo]))

    create table(:user_token) do
      add(:token, :binary, null: false)
      add(:context, :string, null: false)
      add(:sent_to, :string)

      add(:user_cpf, references(:user, column: :cpf, type: :citext, on_delete: :delete_all),
        null: false
      )

      timestamps(updated_at: false)
    end

    create(index(:user_token, [:user_cpf]))
    create(unique_index(:user_token, [:context, :token]))
  end
end

Pesquisador migration:

defmodule Fuschia.Repo.Migrations.CreatePesquisador do
  use Ecto.Migration

  def change do
    create table(:pesquisador, primary_key: false) do
      add(:tipo_bolsa, :string, default: "pesquisa", null: false)
      add(:minibiografia, :string, null: false, size: 280)
      add(:link_lattes, :string, null: false)

      add(
        :usuario_cpf,
        references(:user, column: :cpf, type: :citext, on_delete: :delete_all),
        primary_key: true
      )

      add(
        :orientador_cpf,
        references(:pesquisador,
          column: :usuario_cpf,
          type: :citext,
          on_delete: :delete_all
        )
      )

      add(
        :campus_nome,
        references(:campus, on_delete: :nothing, type: :string, column: :nome),
        null: false
      )

      timestamps()
    end

    create(index(:pesquisador, [:orientador_cpf]))
  end
end

And this is the table structure in the db for the user:

table_name  | user
column_name | cpf
data_type   | USER-DEFINED
-[ RECORD 2 ]----------------------------
table_name  | user
column_name | nome_completo
data_type   | character varying
-[ RECORD 3 ]----------------------------
table_name  | user
column_name | data_nascimento
data_type   | date
-[ RECORD 4 ]----------------------------
table_name  | user
column_name | perfil
data_type   | USER-DEFINED
-[ RECORD 5 ]----------------------------
table_name  | user
column_name | last_seen
data_type   | timestamp without time zone
-[ RECORD 6 ]----------------------------
table_name  | user
column_name | ativo
data_type   | boolean
-[ RECORD 7 ]----------------------------
table_name  | user
column_name | password_hash
data_type   | character varying
-[ RECORD 8 ]----------------------------
table_name  | user
column_name | confirmed
data_type   | boolean
-[ RECORD 9 ]----------------------------
table_name  | user
column_name | contato_id
data_type   | bigint
-[ RECORD 10 ]---------------------------
table_name  | user
column_name | created_at
data_type   | timestamp without time zone
-[ RECORD 11 ]---------------------------
table_name  | user
column_name | updated_at

and pesquisador:

-[ RECORD 1 ]----------------------------
table_name  | pesquisador
column_name | tipo_bolsa
data_type   | USER-DEFINED
-[ RECORD 2 ]----------------------------
table_name  | pesquisador
column_name | minibiografia
data_type   | character varying
-[ RECORD 3 ]----------------------------
table_name  | pesquisador
column_name | link_lattes
data_type   | character varying
-[ RECORD 4 ]----------------------------
table_name  | pesquisador
column_name | usuario_cpf
data_type   | USER-DEFINED
-[ RECORD 5 ]----------------------------
table_name  | pesquisador
column_name | orientador_cpf
data_type   | USER-DEFINED
-[ RECORD 6 ]----------------------------
table_name  | pesquisador
column_name | campus_nome
data_type   | character varying
-[ RECORD 7 ]----------------------------
table_name  | pesquisador
column_name | created_at
data_type   | timestamp without time zone
-[ RECORD 8 ]----------------------------
table_name  | pesquisador
column_name | updated_at
data_type   | timestamp without time zone

These two definitions disagree - the schema expects a field named pesquisador_usuario_cpf (and with define_field: false you’ll need to include a field call for it), while the migration creates a column named usuario_cpf.

1 Like

yeah! that’s because ecto concatenates the table name + field name to a associated schema :confused:

changing the belongs_to to match the migration I receive the same error:

** (EXIT from #PID<0.979.0>) an exception was raised:
         ** (Ecto.QueryError) deps/ecto/lib/ecto/association.ex:765: field `pesquisador_usuario_cpf` in `where` does not exist in schema Fuschia.Entities.Pesquisador in query:

     from p0 in Fuschia.Entities.Pesquisador,
       where: p0.pesquisador_usuario_cpf == ^"264.722.590-70",
       order_by: [asc: p0.pesquisador_usuario_cpf],
       select: {p0.pesquisador_usuario_cpf, p0}

             (elixir 1.12.1) lib/enum.ex:2356: Enum."-reduce/3-lists^foldl/2-0-"/3
             (elixir 1.12.1) lib/enum.ex:1675: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
             (elixir 1.12.1) lib/enum.ex:2356: Enum."-reduce/3-lists^foldl/2-0-"/3
             (ecto 3.6.2) lib/ecto/repo/queryable.ex:208: Ecto.Repo.Queryable.execute/4
             (ecto 3.6.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
             (ecto 3.6.2) lib/ecto/repo/preloader.ex:234: Ecto.Repo.Preloader.fetch_query/8
             (elixir 1.12.1) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
             (elixir 1.12.1) lib/task/supervised.ex:35: Task.Supervised.reply/5
             (stdlib 3.15) proc_lib.erl:226: :proc_lib.init_p_do_apply/3