StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs it possible to define virtual attributes in activerecord corresponding to SQL expressions?
text
Body
copied!<p>I'm after something like virtual attribute, but that would work on the database level: say I have a field <code>age</code> and I would like to add a "virtual field" <code>age_quintile</code> which equals <code>age/5</code>, but in such a way that it is possible to say:</p> <pre><code>Person.select(:age_quintile,"agv(height)"). group(:age_quintile). order(:age_quintile) </code></pre> <p>corresponding to: </p> <pre><code> SELECT (age/5) as age_quintile, avg(height) FROM persons GROUP BY (age/5) ORDER BY (age/5); </code></pre> <p>or </p> <pre><code>Person.maximum(:age_quintile) </code></pre> <p>corresponding to </p> <pre><code>SELECT max(age/5) FROM persons; </code></pre> <p>So, I imagine I would declare such attributes in the model, like:</p> <pre><code>class Person < ActiveRecord::Base ... magic_attribute :age_quintile, :integer, 'age/5' end </code></pre> <p>where the last bit is an SQL expression and type is necessary for casting from strings.</p> <p>Is there a way to do that with vanilla ActiveRecord or with some gem?</p> <h2>Update</h2> <p>The reason for wishing to declare such attributes in the model, and not - as suggested - use an aliased expression verbatim in select is that we would like the attributes participate in a generic query API and appear to the user of the API as any other attribute. So the following should be possible:</p> <pre><code>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 </code></pre> <p>and a query string <code>?group=age_quintile&measurements=height</code> would result in:</p> <pre><code> SELECT (age/5) as age_quintile, min(height), avg(height), max(height) FROM persons GROUP BY (age/5) ORDER BY (age/5); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload