Rails 3 - Expression-based Attribute in Model

How do I define a model attribute as an expression of another attribute?

Example:

Class Home < ActiveRecord::Base
   attr_accessible :address, :phone_number

Now I want to be able to return an attribute like :area_code, which would be an sql expression like "substr(phone_number, 1,3)".

I also wa开发者_如何学Cnt to be able to use the expression / attribute in a group by query for a report.

This seems to perform the query, but does not return an object with named attributes, so how do I use it in a view?

Rails Console:

@ac = Home.group("substr(phone_number, 1,3)").count
=> #<OrderedHash {"307"=>3, "515"=>1}>

I also expected this to work, but not sure what kind of object it is returning:

@test = Home.select("substr(phone_number, 1,3) as area_code, count(*) as c").group("substr(phone_number, 1,3)")
=> [#<Home>, #<Home>]

To expand on the last example. Here it is with Active Record logging turned on:

>Home.select("substr(phone_number, 1,3) as area_code, count(*) as c").group("substr(phone_number, 1,3)")

Output:
Home Load (0.3ms) SELECT substr(phone_number, 1,3) as area_code, count(*) as c FROM "homes" GROUP BY substr(phone_number, 1,3)
=> [#<Home>, #<Home>]

So it is executing the query I want, but giving me an unexpected data object. Shouldn't I get something like this?

[ #<area_code: "307", c: 3>, #<area_code: "515", c: 1> ]

you cannot access to substr(...) because it is not an attribute of the initialized record object.

See : http://guides.rubyonrails.org/active_record_querying.html "selecting specific fields"

you can workaround this this way :

@test = Home.select("substr(phone_number, 1,3) as phone_number").group(:phone_number)

... but some might find it a bit hackish. Moreover, when you use select, the records will be read-only, so be careful.

if you need the count, just add .count at the end of the chain, but you will get a hash as you already had. But isn't that all you need ? what is your purpose ?

You can also use an area_code column that will be filled using callbacks on create and update, so you can index this column ; your query will run fast on read, though it will be slower on insertion.