我正在寻找类似虚拟属性的东西,但这可以在数据库级别上工作:假设我有一个字段age
我想添加一个“虚拟字段”age_quintile
这等于age/5
,但可以这样说:
Person.select(:age_quintile,"agv(height)").
group(:age_quintile).
order(:age_quintile)
对应于:
SELECT (age/5) as age_quintile, avg(height)
FROM persons
GROUP BY (age/5)
ORDER BY (age/5);
or
Person.maximum(:age_quintile)
对应于
SELECT max(age/5)
FROM persons;
所以,我想我会在模型中声明这些属性,例如:
class Person < ActiveRecord::Base
...
magic_attribute :age_quintile, :integer, 'age/5'
end
其中最后一位是 SQL 表达式,并且类型对于从字符串进行转换是必需的。
有没有办法用普通的 ActiveRecord 或一些 gem 来做到这一点?
Update
希望在模型中声明此类属性,而不是按照建议在 select 中逐字使用别名表达式的原因是,我们希望这些属性参与通用查询 API,并向 API 用户显示为任何其他属性。所以以下应该是可能的:
class PeopleController < ApplicationController
def search
group_columns = params[:group].split(" ") # age_quintile could be one of
measurements = params[:measurements].split(" ") # height could be one of
aggregates = %w[min avg max]
select_columns = measurement.map{|m|
aggregates.map{|fn| "#{fn}(#{m})"}
}.flatten
render :json => Person.
select( group_columns + select_columns ).
group(group_columns).
search(group_columns)
end
end
和一个查询字符串?group=age_quintile&measurements=height
会导致:
SELECT (age/5) as age_quintile, min(height), avg(height), max(height)
FROM persons
GROUP BY (age/5)
ORDER BY (age/5);