Problems with the join in my API

Phoenix Forum Questions / Help
,
1

I am currently trying to add a route to my JSON API which will have to return content from 2 tables in a response. From what I understand in SQL, it should be a job for a JOIN (INNER JOIN I think).
The two problems I met are the following :

  • My INNER JOIN query does appear in log, but the columns fron the second table aren’t returned. And doing a SELECT of the two whole table seem tedious and error prone.
  • I don’t know how shoudl I add a new render pattern in my views, and a lot of things I tried didn’t work.

Here are my codes

 query = from exam in Examen,
            join: patient in Patient,
            on: patient.id == exam.patient_id,
            where: exam.day == ^day and exam.modality == ^tag
    Repo.all(query)

[debug] QUERY OK source="examens" db=10.3ms decode=1.1ms queue=3.2ms idle=764.2ms
SELECT e0."id", e0."day", e0."modality", e0."patient_id", e0."inserted_at", e0."updated_at" FROM "examens" AS e0 INNER JOIN "patients" AS p1 ON p1."id" = e0."patient_id" WHERE ((e0."day" = $1) AND (e0."modality" = $2)) ["08332022", "DICOMSTUFP"]

Controller

#This pattern use a inner join
  def index(conn, %{"day" => day, "tag" => tag}) do
    examens = Examens.list_examens_by_day_and_tag(day, tag)
    render(conn, "index.json", examens_with_patients: examens)
  end

View

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

  # Render for INNER JOIN examens x patients
  def render("index.json", %{examens_with_patients: examens}) do
    IO.inspect("Rendered Index")
    %{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_with_patient: examen}) do
    %{
      id: examen.id,
      modality: examen.modality,
      day: examen.day,
      patient_id: examen.patient_id,
      nom: examen.patient.nom,
      prenom: examen.patient.prenom,
      birthday: examen.patient.birthday
    }
  end

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

The desired behavior, considering a table patient with the following fields (id, fname, lname, bday) would be
to have my query return records contain at least (id, day, modality, fname, lname, bday), and then a render fonction printing those in a json (without losing my other render patterns which are used by others controller patterns)

What are your thoughts ?

2

You need to do a preload, not a join…

…or use a select I guess.

1 Like
3

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.

4

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
1 Like
5

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]
6

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
7

You need to remove this…

Because of this…

As You cannot use both at the same time.

1 Like
8

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
2 Likes
9

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

1 Like
10

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)

11

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.

1 Like
12

Can’t edit the post anymore, but just wanted to bring some informations about the render stuff.

It is possible to split the views used by a controller in multiples files, despite the mentionned mapping in the Phoenix documentation (CF second update of the earlier post). The thing is, your conn will automatically try to reach your default view (named after your controller) when calling render in said controller. However, using the put_view function in a pattern allow you to manually set the view file used by the pattern.

Ex:

render(conn, "index.json", examens: examens) # Use default view

# Can become :
conn
|> put_view(ExamenSecondView) # Replace the default associated view in plug
|> render("index.json", examens: examens) # Use index from second view
1 Like