Rails：find_by_sql

      # Executes a custom SQL query against your database and returns all the results.  The results will
      # be returned as an array with columns requested encapsulated as attributes of the model you call
      # this method from .  If you call <tt>Product.find_by_sql</tt> then the results will be returned in
      # a Product object with the attributes you specified in the SQL query.
      #
      # If you call a complicated SQL query which spans multiple tables the columns specified by the
      # SELECT will be attributes of the model, whether or not they are columns of the corresponding
      # table.
      #
      # The +sql+ parameter is a full SQL query as a string.  It will be called as is, there will be
      # no database agnostic conversions performed.  This should be a last resort because using, for example,
      # MySQL specific terms will lock you to using that particular database engine or require you to
      # change your call if you switch engines.
      #
      # ==== Examples
      #   # A simple SQL query spanning multiple tables
      #   Post.find_by_sql "SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id"
      #   > [#<Post:0x36bff9c @attributes={"title"=>"Ruby Meetup", "first_name"=>"Quentin"}>, ...]
      #
      #   # You can use the same string replacement techniques as you can with ActiveRecord#find
      #   Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]
      #   > [#<Post:0x36bff9c @attributes={"first_name"=>"The Cheap Man Buys Twice"}>, ...]
      def find_by_sql(sql)
        connection.select_all(sanitize_sql(sql), "#{name} Load").collect! { |record| instantiate(record) }
      end

 

上面内容摘自Rails源代码。

 

从红色的部分可以看出，find_by_sql返回的结果是一个数组（每个数组包含一个Hash类型实例变量@attribute，所有信息都存储在该变量中）

 

我在Student Controller中加入了一下代码片段

@count = Student.find_by_sql("select * from students")

 

执行后可以看到如下结果：

 

 

很明显，如果我们想获取到记录，可以这样：(获取数组排在n位的元素)

@count[n].attributes[:name]

 

当然遍历出来也很简单了。

 

如果把代码改成：

 

 

则@count的结构如下：

 

 

 

对于SQL注入的问题，在find_by_sql的源代码中有这么一个介绍：

# # You can use the same string replacement techniques # Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date] # > [#<Post:0x36bff9c @attributes={"first_name"=>"The Cheap Man Buys Twice"}>, ...]

 

很明显，ruby find的防注入方法在这里依然通用。

有时候为了拼接方便，可以使用hash的方法实现这一段。如下：

Post.find_by_sql ["SELECT title FROM posts WHERE author = :Author AND created > :Created", {:Author=>"test", :Created=>"test"}] # Or sql = "SELECT title FROM posts WHERE author = :Author AND created > :Created" value = Hash.new value[:Author] = "test" value[:Created] = "test" Post.find_by_sql [sql,value]

 

一点小小问题，注意find_by_sql跟[]一定要留空格或加括号，不然会把find_by_sql[ 看成一个method, 导致参数问题错误。