CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordcalculated-columns
kim3er
kim3er

Reputation: 6476

Access SQL computed columns through ActiveRecord

I have a Person model, which includes a property representing the data of birth (birth_date).

I also have a method called age(), which works out the current age of the person.

I now have need to run queries based on the person's age, so I have replicated the logic of age() as a computed column in MySQL.

I cannot workout how I would make this additional column part of the default select statement of the model.

I would like to be able to access the age as if it were a native property of the Person model, to perform queries against it and access the value in my views.

Is this possible, or am barking up the wrong tree?

I thought I might be able to define additional fields through default_scope or scope, but these methods seem to only recognise existing fields. I also tried default_scope in tandem with attr_assessor.

Possible workarounds I've considered but would prefer not to do:

There must be a way to define dynamic fields through SQL that I can access through the model by default.

Any help would be appreciated.

Rich

UPDATE

An example of my failed attempt to utilise scopes:

default_scope :select => "*, 2 as age"

attr_accessor :age

age is blank, I assume because scopes only deal with limiting, not extending.

Upvotes: 5

Views: 2989

Answers (2)

Michael K Madison
Michael K Madison

Reputation: 2209

I'm not an expert by any stretch, but it seems you want:

class Person < ActiveRecord::Base

scope :exact_age, lambda { |a| where('age = ?', a) }
scope :age_gt, lambda { |a| where('age > ?', a) }

but that said, I've just started looking at Arel, seems pretty cool

Upvotes: 1

Gerry
Gerry

Reputation: 5336

kim3er your solution to your problem is simple. Follow these steps:

Loose the attr_accessor :age from your model. You simply don't need it.

Leave the default scope at the Person model: default_scope :select => "*, 2 as age"

Lastly open up a console and try

p = Person.first
p.age
=> 2

When you define a select using as, Rails will automagically add those methods on the instances for you! So the answer to your question:

There must be a way to define dynamic fields through SQL that I can access through the model by default.

is:

Rails

Upvotes: 4

Related Questions