Rails中查询mysql的一点技巧

ActiveRecode允许直接调用mysql的一些语法。这样一些例如sum avg count的mysql语法就可以非常方便的得以表达。

示例如下：

Person.average('age') 
# => 35.8

支持的方法如下：

1. # average
2. # calculate
3. # count
4. # maximum
5. # minimum
6. # sum

求最小值通过类似group by的语法：

      values = Person.maximum(:age, :group => 'last_name')
      puts values["Drake"]
      => 43

      drake  = Family.find_by_last_name('Drake')
      values = Person.maximum(:age, :group => :family) # Person belongs_to :family
      puts values[drake]
      => 43

      values.each do |family, max_age|
      ...
      end

其中允许的参数包括：

1.     * :conditions - 和Finder语法一样，例如"administrator = 1" or [ "user_name = ?", username ].
2.     * :include: Eager loading, see Associations for details. Since calculations don‘t load anything, the purpose of this is to access fields on joined tables in your conditions, order, or group clauses.
3.     * :joins - An SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id". (Rarely needed). The records will be returned read-only since they will have attributes that do not correspond to the table‘s columns.
4.     * order - 用于排序 "created_at DESC, name" (really only used with GROUP BY calculations).
5.     * :group - 分组 Uses the GROUP BY SQL-clause.
6.     * :select - By default, this is * as in SELECT * FROM, but can be changed if you for example want to do a join, but not include the joined columns.
7.     * :distinct - Set this to true to make this a distinct calculation, such as SELECT COUNT(DISTINCT posts.id) …

示例如下：

  Person.calculate(:count, :all) # The same as Person.count
  Person.average(:age) # SELECT AVG(age) FROM people...
  Person.minimum(:age, :conditions => ['last_name != ?', 'Drake']) # Selects the minimum age for everyone with a last name other than 'Drake'
  Person.minimum(:age, :having => 'min(age) > 17', :group => :last_name) # Selects the minimum age for any family without any minors
  Person.sum("2 * age")

count应用如下：

  Person.count(:conditions => "age > 26")
  Person.count(:conditions => "age > 26 AND job.salary > 60000", :include => :job) # because of the named association, it finds the DISTINCT count using LEFT OUTER JOIN.
  Person.count(:conditions => "age > 26 AND job.salary > 60000", :joins => "LEFT JOIN jobs on jobs.person_id = person.id") # finds the number of rows matching the conditions and joins.
  Person.count('id', :conditions => "age > 26") # Performs a COUNT(id)
  Person.count(:all, :conditions => "age > 26") # Performs a COUNT(*) (:all is an alias for '*')