Problems with the join in my API

You need to do a preload, not a join…

…or use a select I guess.

I read about preload, and assoc, but it need a “belong_to”, which I didn’t have in my “Examen” schema.
I used a generator to create my schema, and used the flag for a foreign key, so My schema is like that

def change do
    create table(:examens) do
      add :modality, :string
      add :day, :string
      add :patient_id, references(:patients, on_delete: :nothing)

      timestamps()
    end

    create index(:examens, [:patient_id])
  end

I’m a bit scared of just breaking everything if I add a constraint for an association, and I thought the references was already bringing enough informations.

If I use preload instead of join, it end like that

query = from exam in Examen,
            #join: patient in Patient,
            #on: patient.id == exam.patient_id,
            where: exam.day == ^day and exam.modality == ^tag,
            preload: [:patients] # patient, patient_id and patients atoms doesn't match
    Repo.all(query)

schema Worklist.Examens.Examen does not have association :patients

And sure, I can use a select, so if it is too tedious to add said constraint, I’ll do that, but I want to try the right way.

You can reuse the results of the join in the preload:

query = from exam in Examen,
            join: patients in Patient,
            on: patients.id == exam.patient_id,
            where: exam.day == ^day and exam.modality == ^tag,
            preload: [patients: patients] # Reusing results from join above

I still don’t get it. The preload can supposedly work with my join as argument, but I still get an error about a inexistant field :

field `Worklist.Examens.Examen.patients` in preload is not an association in query:

from e0 in Worklist.Examens.Examen,
  join: p1 in Worklist.Patients.Patient,
  on: p1.id == e0.patient_id,
  where: e0.day == ^"08332022" and e0.modality == ^"DICOMSTUFP",
  select: e0,
  preload: [patients: p1]

Also, I tried adding a "belongs_to in my Schema, but couldn’t make it work either

defmodule Worklist.Examens.Examen do
  use Ecto.Schema
  import Ecto.Changeset
  alias Worklist.Patients.Patient # as Patient, now callable without the long name

  schema "examens" do
    field :day, :string
    field :modality, :string
    field :patient_id, :id

    timestamps()

    belongs_to :patient, Patient
  end

  @doc false
  def changeset(examen, attrs) do
    examen
    |> cast(attrs, [:modality, :day, :patient_id])
    |> validate_required([:modality, :day])
  end
end

== Compilation error in file lib/worklist/examens/examen.ex ==
** (ArgumentError) field/association :patient_id is already set on schema
    (ecto 3.7.1) lib/ecto/schema.ex:2137: Ecto.Schema.put_struct_field/3
    (ecto 3.7.1) lib/ecto/schema.ex:1890: Ecto.Schema.define_field/4
    (ecto 3.7.1) lib/ecto/schema.ex:1977: Ecto.Schema.__belongs_to__/4 
    lib/worklist/examens/examen.ex:13: (module)
    (elixir 1.13.3) lib/kernel/parallel_compiler.ex:346: anonymous fn/5 in Kernel.ParallelCompiler.spawn_workers/7

You need to remove this…

Because of this…

As You cannot use both at the same time.

They’re able to co-exist when setting define_field: false on the belongs_to assoc. This allows for setting column-specific configuration on the field call that belongs_to does not have, such as :autogenerate or :redact

In this case,

schema "examens" do
    field :day, :string
    field :modality, :string
    field :patient_id, :id

    timestamps()

    belongs_to :patient, Patient, define_field: false
  end

is the same as

schema "examens" do
    field :day, :string
    field :modality, :string

    timestamps()

    belongs_to :patient, Patient
  end

Oh, nice to know… I have never used define_field: false

For those who think this topic can help them :
It seems that “belongs_to” will autoname itself “patient_id”, and replacing the field in the schema by the belongs_to relation doesn’t break anything else (as it is juste a mapping)

Made it work. To synthetise everything, here are the step I followed. I still advice to read the whole thread, as there is good to know informations in every response. Anyways.

• Replaced the field :patient_id, :id in my Examen Schema by a belongs_to :patient, Patient
• Added preload: [patient: patient] in my Ecto query under the “where” line

The others modifications proposed weren’t saved, but were still very useful to understand more about Ecto in general.

There is still work for the view. I’ll update this message as soon as I make progress.

UPDATE : I made the view work. My module look like this :

defmodule WorklistWeb.ExamenView do
  use WorklistWeb, :view
  alias WorklistWeb.ExamenView

  # Render for INNER JOIN examens x patients. Should be refactored in another View file.
  def render("index.json", %{examens_with_patients: examens}) do
    %{data: render_many(examens, ExamenView, "examen_with_patient.json")}
  end

  def render("show.json", %{examen_with_patient: examen}) do
    %{data: render_one(examen, ExamenView, "examen_with_patient.json")}
  end


  def render("index.json", %{examens: examens}) do
    %{data: render_many(examens, ExamenView, "examen.json")}
  end

  def render("show.json", %{examen: examen}) do
    %{data: render_one(examen, ExamenView, "examen.json")}
  end

  def render("examen_with_patient.json", %{examen: examen}) do
    %{
      id: examen.id,
      modality: examen.modality,
      day: examen.day,
      patient_id: examen.patient_id,
      birthday: examen.patient.birthday,
      insee: examen.patient.insee,
      fname: examen.patient.prenom,
      lname: examen.patient.nom
    }
  end

  def render("examen.json", %{examen: examen}) do
    %{
      id: examen.id,
      modality: examen.modality,
      day: examen.day,
      patient_id: examen.patient_id
    }
  end
end

There is room for improvement, and I think I’ll put the examen_with_patient stuff in another view, but I reached my desired result. Marking this post as solution.

SECOND UPDATE : Forgot about this detail : " Note the controller name (HelloController), the view name (HelloView), and the template directory (hello) all follow the same naming convention and are named after each other." from the Request Life Style Guide of the documentation.