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:
- postgresql - Self referencing association in Phoenix using Ecto - Stack Overflow
- join - Ecto query for self-referencing association - Stack Overflow
- Self Referencing Association in Phoenix / Ecto - #4 by drumusician
- elixir - Ecto - Self reference, has_many through - Stack Overflow
- Custom primary key with preload picks wrong key, what am I doing wrong? Or bug?