[Rails] Howto write an SQL query?

[HenriIV]

Hi,

I wrote a find_by_sql query as I didn't manage to do it with ActiveRecord so I have in city_controller:

  def with_properties
    query = "SELECT distinct c.* FROM cities as c LEFT JOIN properties as p ON p.city_id = c.id WHERE p.city_id IS NOT NULL"
    @cities = City.find_by_sql query
  end 

but I would like to move the sql query to the model city as it is more a class thing than a controller. How to do that?

[vgarcia]

In your City model:

class City < ActiveRecord::Base
  def self.find_with_properties
    find(:all, 
      :select => 'distinct c.*',
      :joins => 'LEFT JOIN properties as p ON p.city_id = c.id',
      :conditions => 'p.city_id IS NOT NULL')
  end
end

You don't even need find_by_sql

Then in your controller, use this:

def with_properties
    @cities = City.find_with_properties
end

[HenriIV]

Great! Thanks Vinnie