DEV Community

Mahendra Choudhary
Mahendra Choudhary

Posted on

Need help with sql (*_*) !

Alt Text
hi dev's

While working on a "hospital management system" demo project using rails.I stucked in a scenario where I need to pull "firstname" from users table. Here is detailed scenario ...

So Here are my tables :

table1 : appointments has following columns 
         id|patient_id|physician_id|
Enter fullscreen mode Exit fullscreen mode
table2: patients table                  table3: physicians table
         id|user_id|...                      id|user_id|...
Enter fullscreen mode Exit fullscreen mode

table4: users (note that users table as 4 types of user admim,patient,physician and nurse )

      id|firstname|lastname|role|.....
Enter fullscreen mode Exit fullscreen mode

** Here user_id refers to users table primary key
** patient_id and physician_id refers to patients and physicians primary key

Now how can i fetch "firstname" from users table from appointments table????

As of now i am using following code to print appointments but this is to costly

# frozen_string_literal: true

module Admin
  # Appointment actions
  class AppointmentsController < ApplicationController
    before_action :new_user

    def index
      @appointments = paginate(fetch_records)
      respond_to do |format|
        format.html { @appointments }
      end
    end

   .
   .
   .

    private

    def fetch_records
      @appointments = current_user.admin? ? Appointment.all :    Appointment.current_user(current_user.physician)

      @appointments.collect do |appointment|
        [
          appointment.id, appointment.patient.user.firstname,
          appointment.physician.user.firstname, appointment.status,
          appointment.appointment_date
        ]
      end
    end
  end
end

Enter fullscreen mode Exit fullscreen mode

rails --versin : rails 5.2.3
ruby --version: 2.6.3

Top comments (2)

Collapse
 
richardpaulhall profile image
richardpaulhall

Did you start by writing a query that gave the results you wanted?
Like:

SELECT firstname
FROM users
WHERE Appointments.patient_id = patients.user_id
AND patients.user_id = user.id

(I do not have all the criteria to write the query you need.)
As your code has no literal SQL, the problem is either your query or your implementation of it in whatever you are writing.

Collapse
 
ohaddahan profile image
ohaddahan

Your query creates an N+1 issue.
Try preloading the associated data:

@appointments = (current_user.admin? ? Appointment.all :    Appointment.current_user(current_user.physician)).
                includes(patient: [:user], physician: [:user])