Rails 3. How to perform a "where" query by a virtual attribute?

Question

I have two models: ScheduledCourse and ScheduledSession.

scheduled_course has_many scheduled_sessions

scheduled_session belongs_to scheduled_course

ScheduledCourse has a virtual attribute...

def start_at
  s = ScheduledSession.where("scheduled_course_id = ?", self.id).order("happening_at ASC").limit(1)
  s[0].happening_at
end

... the start_at virtual attribute checks all the ScheduledSessions that belongs to the ScheduledCourse and it picks the earliest one. So start_at is the date when the first session happens.

Now I need to write in the controller so get only the records that start today and go into the future. Also I need to write another query that gets only past courses.

I can't do the following because start_at is a virtual attribute

@scheduled_courses = ScheduledCourse.where('start_at >= ?', Date.today).page(params[:page])

@scheduled_courses = ScheduledCourse.where('start_at <= ?', Date.today)

SQLite3::SQLException: no such column: start_at: SELECT  "scheduled_courses".* FROM "scheduled_courses"  WHERE (start_at >= '2012-03-13') LIMIT 25 OFFSET 0

Answer 1

You can't perform SQL queries on columns that aren't in the database. You should consider making this a real database column if you intend to do queries on it instead of a fake column; but if you want to select items from this collection, you can still do so. You just have to do it in Ruby.

ScheduledCourse.page(params).find_all {|s| s.start_at >= Date.today}

Answer 2

Veraticus is right; You cannot use virtual attributes in queries.

However, I think you could just do:

ScheduledCourse.joins(:scheduled_sessions).where('scheduled_courses.happening_at >= ?', Date.today)

It will join the tables together by matching ids, and then you can look at the 'happening_at' column, which is what your 'start_at' attribute really is.

Disclaimer: Untested, but should work.

Answer 3

I wonder if this would be solved by a subquery ( the subquery being to find the earliest date first). If so, perhaps the solution here might help point in a useful direction...