Querying calculated fields of ActiveRecord

Rails
1

Querying in O/R Mappers

I’m confused about performing queries with O/R Mappers, when properties
don’t map directly to database fields.

Cases like:
* calculated fields ( eg def get_age(as_of = now) ; as_of - birthday ;
end -> method returns result of calculation, not simple accessor)
* different subclasses implement field differently (some may store it
directly, some may calculate it)
* Methods like def zip_code ; address.zip_code; end - which access the
fields of a member object (demeter friendly)

Let’s say I need to get all people whose age as of today is >18, or all
people whose zipcode is 01234.

I can think of two ways:
A) Retrieve all the objects, and run the method on each one.
B) Write special SQL to do the query.

The obvious downside to A is performance.

The obvious downside to B is that it ruins all the benefits of OO
encapsulation, demeter, inheritance, etc. I need to incorporate all of
that knoweledge into the finder methods. When different subclasses
implement the field differently, this gets into some pretty messy SQL as
well.

How do the ActiveRecord pro’s do it?

2

On 11/28/05, List R. [email protected] wrote:

    * Methods like def zip_code ; address.zip_code; end - which access the

The obvious downside to B is that it ruins all the benefits of OO
encapsulation, demeter, inheritance, etc. I need to incorporate all of
that knoweledge into the finder methods. When different subclasses
implement the field differently, this gets into some pretty messy SQL as
well.

How do the ActiveRecord pro’s do it?

ActiveRecord lets you very easily drop down to SQL to fine tune your
queries:

dynamic finders for every table attribute

Developer.find_by_zip_code(‘01234’)
Developer.find_all_by_zip_code(‘01234’)

Developer.find(:all, :conditions => ['lines_of_code > ? and zip_code =
?, 50, ‘01234’])

automatically filters to current project

@project.developers.find(:all, :conditions => ['lines_of_code > ? and
zip_code = ?, 50, ‘01234’])

class Project < AR::Base
has_many :developers
has_many :active_developers,
:class_name => ‘Developer’,
:conditions => ‘lines_of_code > 50’
…

@project.active_developers

As always, consult the documentation:

http://rails.rubyonrails.com/classes/ActiveRecord/Base.html
http://rails.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html

–
rick
http://techno-weenie.net

3

Right, but, if you read the original post, the question is in cases when
the fields don’t map directly to SQL fields, but are instead calculated.
This occurs either do to encapsulation, polymorphism, or
inheritance/subclassing.

An example:

  • A price method, which might be calculated (not stored!), and be
    calculated differently for various subclasses

Again, the two methods I suggested each have drawbacks. Loading each
object and checking the method (A):

Products.find_all.select { |p| p.price < 30 }

works great in terms of OO, but is very slow.

Writing custom SQL (B) improves performance, but convolutes the whole OO
thing. You can no longer encapsulate price() in to each subclass, but
need to define it all in some whopping SQL statement, which would
incorporate each subclasses’ price() method in SQL, and invoke the
correct one based on the type field…

4

Maybe use :select ?

something like this i think
:select => “table.*, table.afield + table.afield2 AS calculated_result”

5

I realize this thread is quite old, but I recently solved a similar
problem, and ran into similar difficulties.

In my situation, I wanted to return, along with other attributes, the
distance between an address and another fixed coordinate on Earth.
Performance is high on my list, so I wrote a pl/pgsql function to avoid
excessive marshalling (and for convenience), but when I started to port
my PHP application to RoR, I ran into the problem you describe.

The best solution I could come up with that balances performance, ease
of use and the OO features/limitations of AR was to, in summary:

  1. Create a database view which selects all of the columns from the
    table in which you’re interested, including your calculated column. Be
    sure to alias your calculated column (geo_distance in my case).
  2. Generate an AR class which binds to the view (either naturally by
    naming it appropriately, or by calling ‘set_table_name “my_view”’ in
    code.
  3. Code custom find_* methods as needed. This will give you free
    access to your calculated columns.

Obviously, you can’t update a database view, so don’t even try calling
.save(!) on any MyView objects. I solved my need to update the table
involved in the DB view by generating a second AR class bound to the
table itself.

Happy tRails!

Chris

6

of use and the OO features/limitations of AR was to, in summary:
Obviously, you can’t update a database view, so don’t even try calling
.save(!) on any MyView objects. I solved my need to update the table
involved in the DB view by generating a second AR class bound to the
table itself.

Actually you can if my memory serves me right… you just need to write
triggers that fire for insert/update/delete that modify the underlying
tables instead of the view… now how well that works with Rails I don’t
know, but my memory (years ago) is that once done it’s done…

Might be an option for you… might not.