CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
user273072545345
user273072545345

Reputation: 1566

How to query three different tables to find out specific information

Ok, this is a triple table query, and I am very very confused in how to go about this, let along construct it.

I'm trying to find all the tutoring session tied to a specific email address.

The tables information is listed down beneath.

User, TutorSession, & SessionUser.

TutorSession belongs to User

Users has many SessionUser

Here are the attributes of each table:

User 

                     :id => :integer,
                  :email => :string,
     :encrypted_password => :string,
   :reset_password_token => :string,
 :reset_password_sent_at => :datetime,
    :remember_created_at => :datetime,
          :sign_in_count => :integer,
     :current_sign_in_at => :datetime,
        :last_sign_in_at => :datetime,
     :current_sign_in_ip => :string,
        :last_sign_in_ip => :string,
             :created_at => :datetime,
             :updated_at => :datetime,
             :first_name => :string,
              :last_name => :string,
               :provider => :string,
                    :uid => :string,
             :roles_mask => :integer,
       :last_activity_at => :datetime,
              :time_zone => :string,
               :imported => :boolean,
   :authentication_token => :string,
:getting_started_dismissed => :boolean,
         :schedule_valid => :datetime

 add_index "users", ["email"], :name => "index_users_on_email", :unique => true
 add_index "users", ["first_name"], :name => "index_users_on_first_name"
 add_index "users", ["last_name"], :name => "index_users_on_last_name"
 add_index "users", ["reset_password_token"], :name => "index_users_on_reset_password_token", :unique => true

TutorSession

                  :id => :integer,
        :session_code => :string,
                :name => :string,
        :moderator_pw => :string,
         :attendee_pw => :string,
        :session_type => :string,
              :status => :string,
          :started_at => :datetime,
            :ended_at => :datetime,
   :tutor_pickup_type => :string,
       :recording_url => :string,
          :created_at => :datetime,
          :updated_at => :datetime,
        :requested_by => :integer,
   :subject_full_name => :string,
   :student_dismissed => :boolean,
           :school_id => :integer,
 :tutoring_subject_id => :integer,
:tutor_appointment_id => :integer,
  :white_board_status => :string,
      :session_length => :integer

SessionUser

  t.integer  "tutor_session_id"
  t.integer  "user_id"
  t.string   "usertype"
  t.string   "url"
  t.datetime "login_at"
  t.string   "logout_at"
  t.string   "flag"
  t.datetime "created_at",        :null => false
  t.datetime "updated_at",        :null => false
  t.string   "user_agent_string"
  t.string   "browser"
  t.string   "os"
end

add_index "session_users", ["tutor_session_id"], :name =>    "index_session_users_on_session_id"
add_index "session_users", ["user_id"], :name => "index_session_users_on_user_id"

EDIT

I made a mistaking in stating how the tables were related to each other. Sorry, my head is somewhere else.

SessionUser belongs to both user and tutor_session

TutorSession has many session_users

User has many session_users

Upvotes: 0

Views: 43

Answers (2)

alf
alf

Reputation: 18530

Not sure what your model looks like, but should be as easy as:

User.find_by_email('[email protected]').tutor_sessions

For this to work your models should look something like this:

class User < ActiveRecord::Base
  has_many :sessions, :class_name => SessionUser
  has_many :tutor_sessions, :through => :sessions
end

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Basically, this query:

SELECT s.*
FROM   users         u
JOIN   session_user su ON u.id = su.user_id
JOIN   tutor_session s ON s.id = su.tutor_session_id
WHERE  u.email = 'foo@bar'

Upvotes: 0

Related Questions