CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlruby-on-rails-4
Batman
Batman

Reputation: 6353

Filtering an active record relation

I have an Availabilities class 

class Availability < ActiveRecord::Base
  # Associations
  belongs_to :facility
  has_one :venue, through: :facility
end

In that class I have a search method which I call like this in my controller:

@search = Availability.search(params, get_location)

  def self.search(params, location)
    perform_search(location, params[:activity], params[:start_time], params[:end_time],params[:period])
  end

  def self.perform_search(location, activity, start_time, end_time, period)
    self
      .not_booked
      .after_now
      .close_to(Venue.close_to(location))
      .activity(Activity.find_by_name activity)
      .start_date(validate_start_time start_time)
      .end_date(validate_end_time end_time)
      .within_segment_of_day(period)
      .sorted
  end

Problem: I now need to add an additional filter. Each venue has it's own minimum time (notice_time:integer type) before an availability can be booked.

For example: You can only book an availability as long as we have 48 hour notice from the start time

I tried to accomplish this by adding a new scope: 

scope :after_notice_time, -> {joins{venue}.where{start_time >= (venue.notice_time.hours.from_now)}}

But got error:

[58] pry(main)> Availability.after_notice_time
Availability Load (4.9ms) SELECT "availabilities".* FROM "availabilities" INNER JOIN "facilities" ON "facilities"."id" = "availabilities"."facility_id" INNER JOIN "venues" ON "venues"."id" = "facilities"."venue_id" WHERE "availabilities"."start_time" >= "hours"."from_now"

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "hours" LINE 1: ..."venue_id" WHERE "availabilities"."start_time" >= "hours"."f... ^ : SELECT "availabilities".* FROM "availabilities" INNER JOIN "facilities" ON "facilities"."id" = "availabilities"."facility_id" INNER JOIN "venues" ON "venues"."id" = "facilities"."venue_id" WHERE "availabilities"."start_time" >= "hours"."from_now" => # < Availability::ActiveRecord_Relation:0x5b525ac>

How do I filter the results so that I can check each availabilities venue.notice_time and make sure that my final @search only includes availabilities where their start_time is greater than current time + notice time

Upvotes: 0

Views: 414

Answers (1)

ehoffmann
ehoffmann

Reputation: 798

You could try something like this with plain SQL (this code is PG specific):

scope :after_notice_time, 
-> { joins(:venue)
.where("start_time >= (current_timestamp + venues.notice_time * interval '1 hour') }

Upvotes: 1

Related Questions