CODEWITHSUNDEEP
ruby-on-rails
StillLearningToCode
StillLearningToCode

Reputation: 2461

Rails scopes using many-to-many and joins

I am trying to write a rails query that returns a list of courses taught by a particular professor. the query I am trying to use is:

def self.taught_by(professor_id)
    Course
        .joins(:sections)
        .joins(:professors)
        .where(['profesor_id = ?', professor_id]).select('distinct courses.*')
  end

SQLite3::SQLException: no such column: profesor_id: S...

How can I use a where clause that references a different table? the professor_id is located in the sections table.

with the following .rb files:

Professor:

class Professor < ApplicationRecord
enum status: { sabbatical: 0, available: 1 }

has_many :sections

has_many :courses, :through => :sections
belongs_to :department

validates :name, presence: true
validates :name, length: { in: 4..32 }
validates :name, uniqueness: { case_sensitive: false }

def self.search(term)
  if term
    where('name LIKE ?', "%#{term}%").order('name DESC')
  else
    order('name DESC')
  end
end
end

Section:

 class Section < ApplicationRecord
  has_many :enrollments
  belongs_to :professor
  belongs_to :course

validates_uniqueness_of :professor_id, scope: :course_id

scope :by_professor_id, ->(prof_id) { where('professor_id = ?', prof_id) }
end

Course:

    class Course < ApplicationRecord
  enum status: { planning: 0, offered: 1 }

  scope :offered, -> { where(status: 1) }
  scope :planning, -> { where(status: 0) }

  belongs_to :department
  has_many :sections
  has_many :professors, through: :sections

  validates :title, :number, :status, :description, presence: true
  validates :description, length: { in: 10..500 }
  validates :title, :number, uniqueness: { case_sensitive: false }

  def self.search(term)
    if term
      where('title LIKE ?', "%#{term}%").order('title DESC')
    else
      order('title ASC')
    end
  end

def self.taught_by(professor_id)
Course
    .joins(:sections)
    .joins(:professors)
    .where(['profesor_id = ?', professor_id]).select('distinct courses.*')
end

end

any help would be really appreciated. I'm sort of stuck on this.

Upvotes: 0

Views: 38

Answers (2)

Alexa Y
Alexa Y

Reputation: 1854

You can use the table name as the key in a hash to specify which table the column exists in.

def self.taught_by(professor_id)
  Course
    .joins(:sections)
    .joins(:professors)
    .where(sections: { professor_id: professor_id }).select('distinct courses.*')
end

Upvotes: 2

refeniz
refeniz

Reputation: 595

It looks like you might have a typo in your where... profesor_id = ?, is that correct?

Upvotes: 1

Related Questions