Replace find_by_sql with find (Rails 2.3)

rubyonrails-talk
1

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
2

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

3

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

4

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

5

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