DEV Community

Mahendra Choudhary
Mahendra Choudhary

Posted on

2 3

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

API Trace View

Struggling with slow API calls? 👀

Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

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

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more