Replace find_by_sql with find (Rails 2.3)

I have an
app run ning Rails 2.3 with Ruby 1.8.7.
Upgrading is not in the cards for a while.
I am trying to make it database agnostic so I want
to eliminate the use of find _by_sql.
I have got it down to only one find left but I have not
been able to figure out how to do this by a find.

            I have two tables:

                Reservation                       belongs_to

:space

                    int space_id

                      date startdate

                        date enddate

                                                and a bunch of other

stuff

                      Space - has many reservations

                          containin                                g

a bunch of stuff ab out
spaces

                                                                        I

want to fetch the spaces which are not used
by a reservation meeting certain conditions.

                                                                                              The

current find is:

                                              all_spaces =

find_by_sql("SELECT *
FROM spaces

   WHERE
                                              id NOT IN (SELECT

space_id FROM
reservations

    WHERE
                                              enddate >

‘#{start_dt}’

     AND startdate

< ‘#{end_dt}’)

")

                                                                                                  Is

there a way I can
just use a Space.all
…?

                                              Norm

I can't recall, does Rails 2.3 have named scopes? You may be able to do this with one of those. It's been quite a while since I worked in 2.3.

Walter

It does have named scopes but I am not sure what that would bring to the table. How would one do it using named scopes?

Norm

I was thinking you could create an 'available' scope, and use that to encapsulate your requirements, but I guess I still don't know how you would simplify your query. Maybe do it in two queries, start with a select id from reservations, then pass that into your spaces query as a parameter rather than a subquery.

space_ids = Reservation.where('enddate > ? and startdate > ?', start_dt, end_dt).select(:space_id)
all_spaces = Spaces.where('id not in ?', space_ids)

Walter

It does have named scopes but I am not sure what that would bring to the table. How would one do it using named scopes?

Norm

I can't recall, does Rails 2.3 have named scopes? You may be able to do this with one of those. It's been quite a while since I worked in 2.3.

Walter

I have an app running Rails 2.3 with Ruby 1.8.7. Upgrading is not in the cards for a while.

I feel your pain.

  I am trying to make it database agnostic so I want to eliminate the use of find_by_sql. I have got it down to only one find left but I have not been able to figure out how to do this by a find.

I have two tables:
Reservation belongs_to :space
  int space_id
  date startdate
  date enddate
  and a bunch of other stuff

Space - has many reservations
  containing a bunch of stuff about spaces

I want to fetch the spaces which are not used by a reservation meeting certain conditions.
The current find is:
all_spaces = find_by_sql("SELECT * FROM spaces
                                    WHERE id NOT IN (SELECT space_id FROM reservations
                                                                 WHERE enddate > \'#{start_dt}\'
                                                                 AND startdate < \'#{end_dt}\')
                                    ")
Is there a way I can just use a Space.all ...?

Norm

I think that this named_scope should work though I'm not sure that it can be considered database agnostic. (Walter's might be close, but not until you have ARel to use.

Reservation.class_eval do
  belongs_to :space
end

Space.class_eval do
  has_many :reservations
  named_scope :available_for, lambda{|start_dt, end_dt|
    { :conditions => [["id NOT IN (SELECT space_id FROM reservations",
                       "WHERE reservations.enddate > ?",
                       "AND reservations.startdate < ?)",
                      ].join(" "), start_dt, end_dt] } }
end

Space.available_for(Date.new(2015,5,11), Date.new(2015,5,15))

The alternative would be to have a method on Reservation that returned the space_ids:

Reservation.class_eval do
  belongs_to :space
  def self.for_space_ids_during(start_dt, end_dt)
    find(:all, {
           :conditions => ["enddate > ? AND startdate < ?",
                           start_dt, end_dt],
           :select => :space_id,
         }).map(&:space_id)
  end
end

Space.class_eval do
  has_many :reservations
  named_scope :except, lambda{|ids|
    { :conditions => ["id NOT IN (?)",
                      Reservation.for_space_ids_during(start_dt, end_dt)] } }
end

Or even making that an association extension:

Space.class_eval do
  has_many :reservations do
    def available_for(start_dt, end_dt)
      reject {|rsv| rsv.enddate > start_dt && rsv.startdate < end_dt }
    end
  end
end

But this last bit is certainly the most inefficient and least visually similar to what it sounds like you'd like to have in a modern version of Rails.

In all cases, know that I haven't run this code so I only suggest that it might work. :wink:

-Rob