how to modify complex sql query w/ join into rails 3

Question

I'm working on a car pooling application where users can add lifts and are able to select multiple stops for each lift ( A to B via c, d, e). Now when a user searches the database for a lift the results should also include lifts like 'A to d', 'c to B' or 'c to e' and so on.

I got this working with Rails 2.3.5 using the below code but struggle moving it over to Rails 3. I'm sure there must be a cleaner way to achieve what i'm trying to do and move some code to the model.

it would be great if someone could help me on this one.

class Lift < ActiveRecord::Base
  has_many :stops
end

class Stop < ActiveRecord::Base           
  belongs_to :lift
  belongs_to :city  
end

class City < ActiveRecord::Base
  has_one :stop
end


@lifts = Lift.find(
  :select =>     "lifts.id, bs.city_id as start_city_id, bs2.city_id as destination_city_id",
  :from =>       "lifts",
  :joins =>      "LEFT JOIN stops bs ON lifts.id = bs.lift_id
                  LEFT JOIN stops bs2 ON lifts.id = bs2.lift_id
                  JOIN cities bc ON bs.city_id = bc.id
                  JOIN cities bc2 ON bs2.city_id = bc2.id",
  :include =>    [:stops, :cities],
  :conditions => "bs.lift_id = bs2.lift_id AND bs.position < bs2.position"
                  #uses the position attribute to determine the order of the stops
)

Answer 1

I know its a really simple answer but why not create a scope in the model with these options?

Answer 2

I'm a little confused that you're using find's :select and :include options together. I know for a fact that :select is ignored when you're using :include (think there was a plugin to fix that, though). Also, you're including :cities, a relationship that doesn't appear in your code.

Anyway, if you're wanting to add some scopes to your Lift model, I'd consider switching to the Arel API, which is the preferred method in Rails 3. http://m.onkey.org/2010/1/22/active-record-query-interface

You could stuff that whole query into one scope (don't think you need the :from option - could be wrong):

class Lift
...
  scope :with_all_stops, select('lifts.id, bs.city_id as start_city_id, bs2.city_id as destination_city_id').\
                         joins('LEFT JOIN stops bs ON lifts.id = bs.lift_id ' +
                               'LEFT JOIN stops bs2 ON lifts.id = bs2.lift_id ' +
                               'JOIN cities bc ON bs.city_id = bc.id ' +
                               'JOIN cities bc2 ON bs2.city_id = bc2.id').\
                         includes(:stops, :cities).\
                         where('bs.lift_id = bs2.lift_id AND bs.position < bs2.position')
...
end

Then just call Lift.with_all_stops. Or you could chain on your own conditions: Lift.with_all_stops.where('cities.name="Topeka"'). It's really powerful.

If there are parts of that query that are useful on their own (doubtful in this particular case), you could break them out into their own scopes, then chain them all together when you call. Or chain them together in another scope, and just call it. Like I said, Arel can be really powerful.