SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [Rails] Howto write an SQL query?

    Hi,

    I wrote a find_by_sql query as I didn't manage to do it with ActiveRecord so I have in city_controller:
    PHP Code:
      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?

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    In your City model:
    Code:
    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:
    Code:
    def with_properties
        @cities = City.find_with_properties
    end

  3. #3
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great! Thanks Vinnie


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •