Unable to get the data from DB in a required format using Rails

Question

I have a rails application where I have 3 models and I'm not able to fetch the data in a required format.

Rails Models

1. Model 1 - Investigation (id, name, created_at, updated_at)
2. Model 2 - Appointment(id, patient_id, investigation_date,....)
3. Model 3 - Result (investigation_id, appointment_id, findings)

Relation b/w models -

Appointment has_many results

Results belongs_to appointment & Results belongs_to investigation

Table schema snippet

create_table "appointments", charset: "utf8mb4", force: :cascade do |t|
  t.bigint "patient_id", null: false
  t.date "investigation_date"
end

create_table "results", charset: "utf8mb4", force: :cascade do |t|
  t.bigint "appointment_id", null: false
  t.bigint "investigation_id", null: false
  t.string "findings"
end

create_table "investigations", charset: "utf8mb4", force: :cascade do |t|
  t.string "name"
  t.boolean "status", default: true
end

Now what I'm looking for is to display the data in a table on the UI in this format -

Table Row1 (fixed)-

Investigation Date (from appointment table), InvestigationName1(from results table(findings attribute)), InvestigationName2(from results table(findings attribute)), ....

Ex- OUTPUT (on UI)

20-May-2021 100, 120, ....

20-June-2021 90, 120, ....

So far what I have been trying to do is

data = {}
result = []
appointments = Appointment.where("patient_id = ? and investigation_date IS NOT NULL", 
params[:p_id]).order("investigation_date desc").last(5)
appointment_id  = appointments.pluck(:id)
results_data    = Result.where(appointment_id: 
appointments.pluck(:id)).includes(:investigation)
appointments.each do |app|
 results_data.each do |data|
  result << data.findings
 end
 data[app.investigation_date.strftime("%d-%b-%Y")] = result
end

Help me where I'm doing wrong ?

with the suggestion of @Lukas I was able to fetch data but still there is slight problem in matching that data with the table header attaching the image below

In the image the data for the second is not matching up with the header above what I mean is the "okay" belongs to the "E" in the header, "90" belongs to "M" in the header

Answer 1

This is the best I came up with:

   investigations > |  .name  |  .name   . .
appointments        |-------------------
      v             |         |
.investigation_date | results | results
.investigation_date | results | results
      .                                  .
      .                                    .

Make an array of investigations you want to display. Use it to make the header and then use the same array to get the matching results from each appointment. Then you can pull any information from those results, like, findings.

investigations = Investigation.order(:name)

header = ["Date", *investigations.pluck(:name)]

rows = Appointment.includes(results: :investigation).map do |a|
  [
    a.investigation_date,
    *investigations.map do |investigation|
      a.results.select { |r| r.investigation_id == investigation.id }.map(&:findings)
    end
  ]
end

I assume you could have more than one investigation on the same day, so this doesn't work and will overwrite existing dates:

data[app.investigation_date.strftime("%d-%b-%Y")] = result

---

<table>
  <thead>
    <tr>
      <% header.each do |th| %>
        <th> <%= th %> </th>
      <% end %>
    </tr>
  </thead>
  <tbody>
    <% rows.each do |tr| %>
      <tr>
        <td> <%= tr.shift %> </td>       <!-- date -->
        <% tr.each do |td| %>
          <td> <%= td.join(" ") %> </td> <!-- results -->
        <% end %>
      </tr>
    <% end %>
  </tbody>
</table>