Replace find_by_sql with find (Rails 2.3)

[Norm Scherer]

I have an app running 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
  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

[Walter Lee Davis]

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

> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/553BE59C.9040001%40earthlink.net.
> For more options, visit https://groups.google.com/d/optout.

[Norm Scherer]

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

[Walter Lee Davis]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/553E747A.20505%40earthlink.net.

[Rob Biedenharn]

On 2015-Apr-27, at 13:40 , Norm Scherer <norms...@earthlink.net> wrote:

> 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
>
> On 04/25/2015 01:52 PM, Walter Lee Davis wrote:
>> 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
>>
>> On Apr 25, 2015, at 3:06 PM, Norm Scherer <norms...@earthlink.net> 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.

>>> 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. ;-)

-Rob

>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
>>> To post to this group, send email to rubyonra...@googlegroups.com.
>>> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/553BE59C.9040001%40earthlink.net.
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/553E747A.20505%40earthlink.net.