Problems with the join in my API

Questions & Help Questions
, ,
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 ?