Replace find_by_sql with find (Rails 2.3)

Rails
1

I have an app running Rails 2.3 with Ruby 1.8.7. Upgrading isnot 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:
Reservationbelongs_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

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

On 2015-Apr-27, at 13:40 , Norm S. [email protected]
wrote:

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.

containing a bunch of stuff about spaces
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