Active Record Table Joins

Question

I have been racking my brain all day and can't get this to work. I am very new to ruby and rails so my apologies for any silly errors.

My problem is I am joining 3 tables together to get a @students object. This works but if I call for example @student.name then 'name' doesn't exist.

Below is my code:

Controller note I have tried using .includes and .join and the same problem happens.

class MyprojectController < ApplicationController
  def show
    @project = Project.find(params[:id])
    @dateformat = '%b %e - %H:%M'
    @user = Student.includes("INNER JOIN researchers ON students.researcher_id = researchers.id
                               INNER JOIN users ON researchers.user_id = users.id").where('user_id = ?', current_user.id)

end

User Model

class User < ApplicationRecord
  include EpiCas::DeviseHelper

  has_many :event_registrations
  has_many :events, through: :event_registrations
  belongs_to :project
  has_many :researchers
  #has_many :students, :through => :researchers
  #has_many :supervisors, :through => :researchers

  # def self.authenticate(username)
  #   where(username: username).first
  # end

end

Researcher Model

class Researcher < ApplicationRecord
  #belongs_to :project
  belongs_to :user
  has_many :supervisor
  has_many :students
end

Student Model

class Student < ApplicationRecord
  #Must have the following
  validates :name, :email, :surname, :email, :supervisor, :registration_number, presence: true
  #ensures unique email addresses
  validates :email, uniqueness: true

  #assosiations
  belongs_to :researcher
end

So every student has a researcher_id and every researcher has a user_id. So the joins should go student->researcher->user and then I want to be able to use all the attributes from all tables in an @user object.

I tried using Student.join(:researcher, :user) but that tried to do a join from the Student table to the researchers table and then tried to join the user table by using a user_id from the student table (but of the user_id is in the researcher table). So i have just done the query myself.

All the data seems to be there but as 'raw attributes'.

Any help would be greatly appreciated!

-James

When you do multiple joins in ActiveRecord then then it gets a little goofy accessing the data. It could be that you need to access it via @student["name"] instead of the usual way. — octopushugs
thanks @octopushugs and I think you are along the right lines but that didn't work :( — James Webb

Puhlze Puhlze · Accepted Answer · 2017-04-20T21:59:53

Rather than try and join things into one return (like you would in sql) use includes so that you can access all your data in fewer queries but you still have access to your data in objects. The point of using an ORM like ActiveRecord is to be able to access your data using objects. The downside of using an ORM is that sometimes it's not as efficient at getting you the exact data you want, because the data is pushing into objects. Using includes provides a sort of middle ground where you can access the data you require in objects and you don't necessarily have to run queries for each association.

Try something like (depending on how you're getting your user id -- I'm assuming from project):

@user = User.includes(researcher: :student).find(project.user_id)

And then you can access things through the normal rails associations:

researcher = @user.researcher

student = researcher.student

I hope that helps and best of luck!

Active Record Table Joins

1 Answers